Solved: Re: data transformation question - Page 2 - Qlik Community

userid128223 · ‎2014-02-11

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

userid128223 · ‎2014-02-11

Hi Jaime

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

jaimeaguilar · ‎2014-02-11

I Just sent it to your email

Sokkorn · ‎2014-02-12

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 · ‎2014-02-12

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 · ‎2014-02-12

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 · ‎2014-02-12

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 · ‎2014-02-12

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 · ‎2014-03-17

Thanks Jamie

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

userid128223 · ‎2014-03-17

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 · ‎2014-03-18

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

thanks