After learning how to connect to a local or remote CSV data source, move on to setting data types.
In this guide, you can find:
- How to define data types in a CSV file
- Supported date formats
- How to create multilevel hierarchies in CSV
- Examples of CSV data with prefixes
Define data types in a CSV file
To define data types for CSV data source, set special prefixes before columns names.
WebDataRocks Pivot Table supports the following prefixes:
| Name | Description |
|---|---|
+ | A string value. Available aggregations: Count and Distinct Count. The values of such a field type will be sorted alphabetically. |
- | A numerical value. It is possible to apply any available aggregation function to this type of field. The values of such a field type will be sorted numerically. |
m+ | This field type is used for months. The months are sorted in natural sorting order, not alphabetically. |
w+ | This field type is used for days of the week. The days are sorted from Sunday to Monday. |
d+ | This field type is used for dates. It is applied to all dates by default. The dates are divided into 3 separate subfields: Year, Month, Day. |
D+ | This field type is used for dates. The following hierarchy will be applied to it then: Year > Month > Day. |
D4+ | This field type is used for dates. The following hierarchy will be applied to it then: Year > Quarter > Month > Day. It is a convenient division of the date for quarterly reports creation. |
ds+ | This field type is used for dates. The dates are displayed as strings which can be formatted using date pattern (default is dd/MM/yyyy). |
t+ | This field type is used for time and can be selected to values. It will be formatted according to HH:mm pattern. Available aggregations: Min, Max, Count, Distinct Count. |
dt+ | This field type is used for date and can be selected to values. It will be formatted according to dd/MM/yyyy HH:mm:ss pattern. Available aggregations: Min, Max, Count, Distinct Count. |
If the type of the data is not defined explicitly, the component determines the type of a column based on the first value of that column. Though the pivot table tries to guess the type correctly, if null is the first value of the column, the results of data processing may be unpredictable. Setting types is a good practice before loading the data into the pivot table.
See the example of a CSV data source with prefixes:
D+Date, -Price, +Product, -Quantity 2018-09-18, 55823, 3.50, Ice Cream, 2 2018-09-21, 44205, 1.20, Muffin, 3 2018-09-21, 44373, 1, Water, 2 2018-09-22, 44434, 2, Brownie, 1 2018-09-23, 44713, 2.25, Coffee, 3 2018-09-24, 44780, 1.60, Green Tea, 1
Supported date formats
The input values of date fields have to be formatted according to ISO 8601 – the International Standard for the representation of dates and time. If the incoming data is formatted according to this standard, the correct interpretation and processing of such a data is guaranteed. Otherwise, the results can be unexpected.
Examples of correct date values:
- For just date:
"2018-01-10" - For date and time:
"2018-01-10T08:14:00"
WebDataRocks supports 5 output date types:
d+D+D4+ds+dt+
Сreating multilevel hierarchies in CSV
CSV data source allows creating multilevel hierarchies from date fields.
If you want to represent a date as a hierarchical one, open your CSV file and set the date’s type to D+ or D4+. The difference between these two types is described above in the guide.
Here is an example with the D+ type:
D+Invoice Date, -Quantity, -Price, +Country 2018-05-15, 3, 329, France 2018-05-16, 4, 139, Italy 2018-05-17, 2, 429, Spain 2018-05-12, 2, 559, Japan
See how the Invoice Date field will be displayed on the pivot table if we load this file to WebDataRocks:
This demo is also available on our CodePen.
To create multilevel hierarchies from fields of other types, consider using a JSON data source.
Examples of CSV data with prefixes
To make everything clear, look through the following example with ds+ and w+ types:
Invoice Number, ds+Invoice Date, -Quantity, -Unit Price, +Country, w+Week Day 43111, 2018-05-15, 3, 329, France, Tuesday 43112, 2018-05-16, 4, 139, Italy, Wednesday 43113, 2018-05-17, 2, 429, Spain, Thursday 43110, 2018-05-12, 2, 559, Japan, Saturday
In this example, we’ve interpreted “Invoice Date” as a date that is displayed as a string and “Week Day” as a day of the week.
After loading the CSV file and configuring the fields in the Field List, the pivot table has the following view:
In this example, it is easy to see that the “Invoice Date” column with the ds+ prefix is formatted according to the dd/MM/yyyy pattern, the values of “Week Day” column with w+ prefix are interpreted as days of the week that are sorted in a natural order, not an alphabetical one.
Now set D+ prefix to “Invoice Date” column to see the difference. The data source will look in the following way:
Invoice Number, D+Invoice Date, -Quantity, -Unit Price, +Country, w+Week Day 43111, 2018-05-15, 3, 329, France, Tuesday 43112, 2018-05-16, 4, 139, Italy, Wednesday 43113, 2018-05-17, 2, 429, Spain, Thursday 43110, 2018-05-12, 2, 559, Japan, Saturday
Now the “Invoice Date” column is split into 3 separate fields: Year, Month, and Day. These fields form a multilevel hierarchy which can be drilled down & up by clicking:
