load * inline [
Plant Product 02012016 02022016 02032016
Atlanta Cars 52 77 23
Dallas Cars 15 27 56
Dallas Bike 15 27 56
] (delimiter is spaces);
T: CrossTable (MonthText, Val, 2) LOAD * Resident S;
Final: Load Plant, Product, Date(Date#(MonthText,'MMDDYYYY'), 'YYYY MM') as Month, Val Resident T;
DROP Table S, T;
Sorry for my inability to communicate - I have the data in a text file downloaded from SAP and load that into a straight table during an automated hourly update. I have tried this with a pivot table but I cannot find any way to sort like this so I believe I am stuck with a straight table.
I tried loading this into a straight table as shown initially [column names PLANT, PRODUCT, 02012016, 02022015, etc.] then sorted by the first date (02012016) column in descending sequence, then the next date column in descending sequence, etc. This is a published application that needs to contain the final table. It is not one created by the users. I need the new "first date" data to be in the same relative column as the previous "first date" so it can be sorted as indicated.
I think I need to load the data with a reference field (maybe something like DATE_FIELD_01 through DATE_FIELD_XX that is linked somehow to the first date column, second date column, etc.). I then need to show the value of the data as the text for the column.
I am sorry I am so much trouble putting this into words.