Getting Started with Data360 Analyze and Excel

Use the Excel File node to read in your Excel file. You will find this under "Input Connectors": 

Just specify the filename, which you can do via the File Picker in the Property panel. 

Recommended Video: Acquiring Excel file and Data Conversion

In the Excel File node, there is an optional property called WorkbookSpec that allows you to specify the worksheets that you want to read in. 

You can keep the data as separate data sets or easily read them in together as a single data set. 

Recommended Article: How to read multiple worksheets from Excel

You can import multiple Excel files at the same time using the Directory List node.

For example, let's say you have an Excel workbook for sales data and there is one for each month - so 12 workbooks in total. Instead of needing to use 12 Excel File nodes to read each file in individually, you can use the Directory List node in conjunction with the Excel File node to easily read in multiple Files at the same time. 

Recommended Article: How-can-I-read-in-multiple-files-from-a-directory?

If you only want to read in specific rows or columns in your Excel data, you can use the WorkbookSpec to specify this information.

Recommended Article: Importing-specific-rows-columns-from-Excel

There are different ways to exclude rows in your data in Data360 Analyze but if rows are blank and offer no value you can avoid reading them into Data360 Analyze in the first place.

Recommended Article: Can-the-Excel-File-node-handle-blank-rows-in-the-middle-of-the-data?

In Data360 Analyze, you can easily rename any of your data fields using the rename output statement. For example:

Or you may wish to use the Modify Field Prefix or Change Metadata nodes.

Recommended Article: Preparing Data – Renaming Fields

A quick and easy way to convert the data types in your data set to more appropriate ones (e.g. converting "Amount" column from string to a number) is to use the Data Converter node (under Aggregation and Transformation). Just connect it to your data and run!

Recommended Video: Acquiring Excel file and Data Conversion

To exclude any columns, just use the exclude output statement.

For example, to exclude the fields 'customer name' and 'address' from my data, I can put the following inside a Transform node:

The great part is, this does not change your source data, which means if you end up needing the columns you previously excluded, you can simply change or remove the exclude statement and re-run the node.

You can use variables to create a new column in your data. 

For example, I want to add a column in my data for the country name because my data includes addresses but not the country. I can put the following inside a Transform node:  

There are various ways to filter your data in Data360 Analyze, including within the Data Viewer or using Script to reference a row position or specify a condition to control the output. 

Data Viewer

When you're viewing your data in the Data Viewer , you can filter your data in a manner that is similar to what you may already be used to in Excel:

In the Data Viewer, the menu for each column header provides an option to apply a Filter to that column, as shown in the image below: 

You are then given the option to specify the Filter: 

If you want this Filter logic to be part of your data flow, you can easily add this to your canvas via the menu in the top right of the Data Viewer as shown in the image below: 

This adds a Filter node to your data flow with the logic already configured. Note that you can also add a Filter node directly by selecting the Filter in the Node Panel, which you will find under Aggregation and Transformation: 

Script

You can also exclude certain rows in your data by referencing either the row position or by using a condition to control the output. 

For example, to only include records in my data where the 'Account' field has the value "Active", I can put the following in a Transform node:

Recommended Article: Exclude specific rows from your data

To sort your data, you can either:

a) Use the Sort node (Aggregation and Transformation)

Recommended Example: C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Sort Node.lna

b) Use the Data Viewer

In the data viewer, you can sort your data by selecting the Sort option in the menu option for the column that you wish to sort. 

You can add this Sort to your data flow via the menu option in the top right of the Data Viewer:

IF statements in Excel are very powerful and you can use them in Data360 Analyze as well just with a slightly different statement structure. 

For example, I have a column 'color' and wanted to create a new column 'category' that would take the value "A" if the color was "Red" and "B" otherwise. 

In Excel, my IF statement would look something like

IF (A3="Red", "A","B")

as shown in the image below: 

In Data360 Analyze, the same logic would be structured like this: 

if 'color' == "Red" then "A" else "B"

as shown in the image below:

As in Excel, I can have nested IF statements in Data360 Analyze as well. 

Following from the example above, in Excel, I might have the following nested IF statement: 

IF(A2="Red","A",IF(A2="Blue","B","C"))

In Data360 Analyze, this would look like this: 

if 'color' == "Red" then "A"
else (if 'color' == "Blue" then "B" else "C")

Help page: Script help > Conditional operators

You can use the Quick Stats node to perform a quick statistical analysis of numeric input data, using the sum, min, max, average, count, range and null count functions. 

For example, I have sales data from all my distributors: 

I want to work out the total sale amount per Distributor as well as the maximum and minimum sale amounts each Distributor made across their submitted orders.

I can drag-and-drop a Quick Stats node onto my canvas and connect to my data source. In the 'FieldList' property, I select the field that I want to run my analysis against, which is the sales amount field 'sale amount ($)'. In 'GroupByExpr' property, I specify 'distributor' as the field to group on, ensuring that the analysis produces one line per unique distributor. 

The Quick Stats node then produces the sum, average, minimum and maximum values of the sale amounts as well as providing the range, count of unique values and count of NULL values: 

Alternatively, you can also use Script to generate these calculations in an Agg Ex node and utilizing the built-in macros such groupSum(), groupCount(), groupMin() and groupMax(). 
You can find an example of these functions in an Agg Ex node installed with Data360 Analyze. 

Recommended Example: C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Agg Ex Node.lna

To calculate a cumulative sum, you can use the groupSum() built-in macro inside an Agg Ex node. 

For example, I have the total amount in sales by week and I want to view a year-to-date sales total.

In an Agg Ex node, the groupSum() function can be applied to the 'weekly amount ($)' field. I'm not grouping by any field here so I can simply set the 'GroupBy' property to 1:

This produces the new column 'CumulativeSum', which is the cumulative sum of the weekly amount:

Recommended Example: C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Agg Ex Node.lna

It's common in Excel to need to use the Transpose option under the Paste menu

In Data360 Analyze, there are two node dedicated to helping you transpose your data and you will find these under Metadata and Structure:

• Pivot Data to Names - Converts input data into field names in the output i.e. transpose rows to columns
• Pivot Names to Data - Converts the field names in the metadata of an input data set into field values in output records. i.e. transpose columns to rows

For example, I have total sales by month for each distributor on unique rows: 

The Pivot - Data to Names node allows me to easily transpose the data such that there is one line per unique Distributor and a column for each month: 

Recommended Example: C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Pivot-Data to Names Node.lna

The Pivot Table node summarizes tabular data values across two specified fields (dimensions) to create a pivot table containing the summarized (aggregated) data, together with sub-totals for each dimension and the grand total.

The input data can be summarized using a range of aggregation functions, specifically: count, sum, min, max and mean.

Recommended Example: 
C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Pivot Table Node.lna

You can append multiple data sets together in a single step using the Cat node, which you will find under Aggregation and Transformation.

Just connect your data sources to the Cat node.

Your data does not need to have the same column headers either. if some data sources have additional columns that you want to keep, simply specify the ConcatenationMode property to Union and any data source that didn't have that column will simply have a NULL value assigned.

Recommended Example:  C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Cat Node.lna

To perform a lookup similar to the VLOOKUP in Excel, you can use the Lookup node in Data360 Analyze, which you will find in the Correlation category.

With the Lookup node, if there are multiple fields to be returned for a single join criteria (e.g. Looking up a product code to get the product name, product category and product cost), I can do this in a single Lookup node and not require multiple lookups the way I would need multiple VLOOKUP functions.

There are additional, more comprehensive ways to join data together in Data360 Analyze that go beyond the joining capability in Excel and you will find these nodes in the Correlation node category. For example, Fuzzy X-Ref node.

Recommended Article: How do I perform an Excel VLOOKUP in Data360 Analyze?

Recommended Example: C:\Program Files\Data3SixtyAnalyze\docs\samples\nodes\Example Lookup Node.lna

Data360 Analyze offers a broad range of Output Connectors such that prepared data can be provisioned in diverse formats and to a range of enterprise data systems including: Flat files, Excel, Databases, Visualization applications (Qlik Sense/Spotfire/Tableau), FTP, SharePoint, Salesforce, Apache Hadoop HDFS/ Spark, Web APIs, Email

You can output data at any step in the data flow to Excel using the Output Excel node or use the Append Excel node to append to an existing Excel file. 

If you wanted to output to a pre-formatted Excel report, with charts, logos etc already created, you can use the option in the Output Excel  node to output using an existing Excel file as a template. 

Recommended Article: Publishing to Excel with a Template