Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bqlikschmidt
New Contributor

Create static columns from dynamic data

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

3 Replies

Re: Create static columns from dynamic data

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

- Marcus

MVP
MVP

Re: Create static columns from dynamic data

something like

S:

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;

bqlikschmidt
New Contributor

Re: Create static columns from dynamic data

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.

Community Browser