topic Re: Create static columns from dynamic data in QlikView

Create static columns from dynamic data

Anonymous — Fri, 05 Feb 2016 15:53:34 GMT

I apologize if this has been answered but I have searched many places and maybe I just don't understand what I am reading. My data looks something like:

Plant Product 02012016 02022016 02032016 ...

Atlanta Cars 52 77 23 ...

Dallas Cars 15 27 56 ...

Every day the date in the column changes. I need to load in such a way that a straight table can be loaded so the now different column names load into the same relative column - oldest first. It appears to me I need to do something like add a static name into the new data and use that in the chart referring to the date as the text for the column. I hope that makes sense.

Thank you - Bob Schmidt

Re: Create static columns from dynamic data

marcus_sommer — Sat, 06 Feb 2016 12:08:28 GMT

You neet to transform your crosstable to a normal table-structure, see: The Crosstable Load.

- Marcus

Re: Create static columns from dynamic data

maxgro — Sat, 06 Feb 2016 13:43:56 GMT

something like

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;

Re: Create static columns from dynamic data

Anonymous — Mon, 08 Feb 2016 13:26:51 GMT

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.