LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Borg_species5618

I have the following input data
Pic1.JPG

I want to create a Pivot table where the date columns (File_Date) is sorted by date order.
side note: (Not sure why but the Pivot node does not like dates in the "NameField" section, therefore I have had to convert (File_Date) field to a string format)

I would like the output to look like this
pic2.JPG

Unfortunately the columns never end up in date order, I have even tried to sort column (File_Date) before creating the Pivot table.

Hope someone can assist

Thankyou in advance

Frank
Attachments:
pic2.JPG
Pic1.JPG

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

If the first account it finds has only a portion of the dates, then those dates will be listed first, followed by other dates as they are found later in the data.

If you create a dummy set of records with say some Customer Number 0000 and make sure that customer #0000 has one record for every possible date, then you can trick the Pivot Names to Data into listing all the output columns in sorted order.

Creating the dummy data can be done quite easily in an Agg node, and then concatenated to the main set of data, then sorted by Customer/Date before pivoting. A filter after the Pivot can then remove the dummy customer, without changing the column order.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rpcwong

Try to do the same thing and using MONTH instead of DATE...
Running into issues of 1, 10, 11, 12, 2, 3, 4, 5, 6.. even if I add dummy records for each customer.
I have to turn the MONTH into string and make them 01, 02, 03,.. 10, 11, 12 in order for this to work.

Just wanted to share my exp.