After learning how to connect to a local or remote CSV data source, move on to setting data types.
To define data types for CSV data source, set special prefixes before columns names.
WebDataRocks Pivot Table supports the following prefixes:
|+||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.|
This field type is used for dates. The dates are displayed as strings which can be formatted using date pattern (default is
This field type is used for time and can be selected to values. It will be formatted according to
This field type is used for date and can be selected to values. It will be formatted according to
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.
WebDataRocks supports 5 output date types:
Examples of correct date values:
- For just date: “2018-01-10”
- For date and time: “2018-01-10T08:14:00”
Example of CSV data with prefixesTo make everything clear, look through the following example with
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 which 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 “Invoice Date” column with
ds+ prefix is formatted
dd/MM/yyyy pattern, the values of “Week Day” column with
w+ prefix are
interpreted as days of the week
which are sorted in a natural order, not an alphabetical one.
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 “Invoice Date” column is split into 3 separate fields: Year, Month, Day. These fields form a multi-level hierarchy which can be drilled down & up by clicking: