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:

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.

WebDataRocks supports 5 output date types:

  1. d+
  2. D+
  3. D4+
  4. ds+
  5. dt+

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 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 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 according to 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.

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 “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: