Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
userid128223
Creator
Creator

data transformation question

I have data in following format.

Store#     StoreName     StoreLocation   num_of_employee       Total Sales     Month     Year

123          Chales           California          55                              100                01          2014

123          Chales           California          75                              200                02          2014

123          Chales           California          100                             500                04          2014

123          Chales           California          44                              600                06          2014

123          Chales           California          22                              700                08          2014

200          Delta             Kansas            55                              1000                01          2014

200          Delta             Kansas            55                              1200                02          2014

200          Delta             Kansas            55                              100                  04          2014

200          Delta             Kansas            55                              1400                10          2014

200          Delta             Kansas            55                              2000                12          2014

............

.........

How can i transform it to this when i read it in qlikview.

Store#     Discription              2014-01            2014-02       2014-03     2014-05    

123         StoreName              Charles           Charles

123        StoreLocation           California         Charles

123         Num_of_employee    55                  75    

123        Total_sales               100                 200

200         StoreName

200        StoreLocation

200         Num_of_employee

200        Total_sales

20 Replies
userid128223
Creator
Creator
Author

Hi Jaime

Can you please email me. I don't see it.

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

I Just sent it to your email

Sokkorn
Master
Master

Hi,

Here is load script

[Source]:

LOAD * Inline [

Store#,     StoreName,     StoreLocation,   num_of_employee,   Total_Sales,     Month,     Year

123,        Chales,         California,          55,               100,           01,      2014

123,        Chales,         California,          75,               200,           02,      2014

123,        Chales,         California,          100,              500,           04,      2014

123,        Chales,         California,          44,               600,           06,      2014

123,        Chales,         California,          22,               700,           08,      2014

200,        Delta,          Kansas,              55,               1000,          01,      2014

200,        Delta,          Kansas,              55,               1200,          02,      2014

200,        Delta,          Kansas,              55,               100,           04,      2014

200,        Delta,          Kansas,              55,               1400,          10,      2014

200,        Delta,          Kansas,              55,               2000,          12,      2014];

[tmpSource]:

NoConcatenate

LOAD *,Year &'-'& Month AS Period Resident [Source];

DROP Table [Source];

DROP Fields Year, Month;

[tmpData]:

CrossTable(Description,Val,2)

LOAD RowNo() As BlockID,* Resident [tmpSource];

DROP Table [tmpSource];

[MapPeriod]:

Mapping

LOAD BlockID,Val As Period Resident [tmpData] Where Match(Description,'Period');

[Data]:

NoConcatenate

LOAD

  Store# &'|'& Description As tmpField,

  ApplyMap('MapPeriod',BlockID) As Period,

  Val

Resident [tmpData] Where Not Match(Description,'Period');

DROP Table [tmpData];

[TMP1]: 

GENERIC LOAD * RESIDENT [Data]; 

 

[tmpResult]: 

LOAD DISTINCT tmpField RESIDENT [Data]; 

 

DROP TABLE [Data]; 

 

FOR i = 0 to NoOfTables() 

TableList: 

LOAD '[' & TableName($(i)) & ']' AS Tablename AUTOGENERATE 1 

WHERE WildMatch(TableName($(i)), 'TMP1.*'); 

NEXT i 

 

FOR i = 1 to FieldValueCount('Tablename') 

LET vTable = FieldValue('Tablename', $(i)); 

LEFT JOIN ([tmpResult]) LOAD * RESIDENT $(vTable); 

DROP TABLE $(vTable); 

NEXT i 

 

DROP Table TableList;

[Result]:

NoConcatenate

LOAD

  SubField(tmpField,'|',1) As Store#,

  SubField(tmpField,'|',2) As Description,

  *

Resident [tmpResult];

DROP Table [tmpResult];

DROP Field tmpField;

See attached sample file.

Regards,

Sokkorn

userid128223
Creator
Creator
Author

Thanks

Jaime

Can you please explain bit on how this work.

So inline table I need to create for all the fields. what does the crossfile word do, how does it manipulate parameters. can you please give me so insight.

can this be done via crosstable GUI interface.

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Suppose you have this in an external file (it can be an inline table too), with the structure you mentioned:

Store#     StoreName     StoreLocation   num_of_employee       Total Sales     Month     Year

123          Chales           California          55                              100                01          2014

123          Chales           California          75                              200                02          2014

123          Chales           California          100                             500                04          2014

123          Chales           California          44                              600                06          2014

123          Chales           California          22                              700                08          2014

200          Delta             Kansas            55                              1000                01          2014

200          Delta             Kansas            55                              1200                02          2014

200          Delta             Kansas            55                              100                  04          2014

200          Delta             Kansas            55                              1400                10          2014

200          Delta             Kansas            55                              2000                12          2014

when you load it to QlikView just change the order of the loaded fields (ultimately this doesn't not affect the structure of data), like this:

CrossTable(Description, Data, 4)

LOAD Store#,

     Month,

     Year,

     Year & '-' & Month as YearMonth,

     TotalSales,

     StoreName,

     StoreLocation,

     Num_of_employee

    

FROM

crosstable.xlsx

(ooxml, embedded labels, table is Sheet1);

crosstable function takes 2 or 3 parameters: the names of the new fields that you will create (in this case description and data, this names can be whatever) and the number of columns that will NOT be transposed. That’s why there’s a 4 in parameters because Store#, Month, Year and the newly created field YearMonth will not be transposed.

After that, you create an inline table with numeric references, that will be used in the visualization table in order to create a case scenario:

LOAD * INLINE [

    DescriptionID, Description

    1, Num_of_employee

    2, StoreLocation

    3, StoreName

    4, TotalSales

];

After reloading script, create a pivot table with Store#, Description (This is the field generated by the crosstable function) and YearMonth as dimensions. In the expression put this:

pick(DescriptionID,

only({$<Description={'Num_of_employee'}>}Data),

only({$<Description={'StoreLocation'}>}Data),

only({$<Description={'StoreName'}>}Data),

only({$<Description={'TotalSales'}>}Data)

)

This will create a case scenario. It means that the calculated expression will be different for every description available. In this example you have 4 possible descriptions, so you have 4 possible expressions. It is important to mention that in expression wizard you have to put the expressions in the same order that is in your inline table where descriptionID is generated (otherwise you will need to define a custom sort order).

Finally you need to put yearmonth as a vertical dimension like in the example I sent you. Like I said, there are many ways to get to the same result, just try what better fits to you,

regards

Anonymous
Not applicable

Is there any reason why not to use below vs your equation?


     pick(DescriptionID,Data,Data,Data,Data)

vs

     pick(DescriptionID,

     only({$<Description={'Num_of_employee'}>}Data),

     only({$<Description={'StoreLocation'}>}Data),

     only({$<Description={'StoreName'}>}Data),

     only({$<Description={'TotalSales'}>}Data)

     )

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

I put it every expression explicitly just to make clear that you can define different expressions for every case, but yes in this particular example it's easier to put what you suggest

userid128223
Creator
Creator
Author

Thanks Jamie

1 more question when you create pivot table. Store# only shows up once, how can you write store # in each field.

userid128223
Creator
Creator
Author

I Use below formula to pull my data. however something strange is happening.

pick(DescriptionID,Data,Data,Data,Data)    // I use Data 99 times as I have 99 fields to pull

It works until 96 fields.

97,98 & 99 field just does not get pulled. is there a limitation.

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Hi, Could you please upload your qvw so I can check what is not working?

thanks