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

1 Solution

Accepted Solutions
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

View solution in original post

20 Replies
userid128223
Creator
Creator
Author

Any clues guys.

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Hi,

you may need to use crosstable and pick function,

check the following example,

regards

Anonymous
Not applicable

See Re: crosstable reversed

You could also use a pivot table.

fkeuroglian
Partner - Master
Partner - Master

You will have to use cross table to create each year as a dimension

good luck!

use the example that Jaime send !

userid128223
Creator
Creator
Author

were is the link to example,  i don't see it.

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

it is the QVW attached at the end of my post

userid128223
Creator
Creator
Author

strange but i dont see attachment. can you please link it again.

thanks

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Here it is:

Not applicable

Hi,

Try below script,

CrossTable(Description,Value,2)

LOAD Store#,

    
Year &'-'& Month AS YearMonth,

    
StoreName,

    
StoreLocation,

    
num_of_employee,

    
[Total Sales]

FROM Data.qvd;



Test2:

Generic

Load Store#,

    
Description,

    
YearMonth,

    
Value

Resident Test1;



Drop Table Test1 ;