topic Re: data transformation question in QlikView

data transformation question

userid128223 — Tue, 11 Feb 2014 22:02:57 GMT

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

Re: data transformation question

userid128223 — Tue, 11 Feb 2014 22:40:19 GMT

Any clues guys.

Re: Re: data transformation question

jaimeaguilar — Wed, 12 Feb 2014 00:02:57 GMT

Hi,

you may need to use crosstable and pick function,

check the following example,

regards

Re: data transformation question

Anonymous — Wed, 12 Feb 2014 00:49:34 GMT

See Re: crosstable reversed

You could also use a pivot table.

Re: data transformation question

fkeuroglian — Wed, 12 Feb 2014 01:36:18 GMT

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

good luck!

use the example that Jaime send !

Re: Re: data transformation question

userid128223 — Wed, 12 Feb 2014 03:23:12 GMT

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

Re: data transformation question

jaimeaguilar — Wed, 12 Feb 2014 03:26:22 GMT

it is the QVW attached at the end of my post

Re: data transformation question

userid128223 — Wed, 12 Feb 2014 03:31:05 GMT

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

thanks

Re: Re: data transformation question

jaimeaguilar — Wed, 12 Feb 2014 03:47:22 GMT

Here it is:

Re: data transformation question

Wed, 12 Feb 2014 03:49:57 GMT

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 ;

Re: Re: data transformation question

userid128223 — Wed, 12 Feb 2014 03:52:49 GMT

Hi Jaime

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

Re: data transformation question

jaimeaguilar — Wed, 12 Feb 2014 04:29:56 GMT

I Just sent it to your email

Re: Re: data transformation question

Sokkorn — Wed, 12 Feb 2014 05:01:23 GMT

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

Re: data transformation question

userid128223 — Wed, 12 Feb 2014 16:47:54 GMT

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.

Re: data transformation question

jaimeaguilar — Wed, 12 Feb 2014 17:09:43 GMT

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

Re: data transformation question

Anonymous — Wed, 12 Feb 2014 17:50:20 GMT

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

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

pick(DescriptionID,

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

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

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

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

)

Re: data transformation question

jaimeaguilar — Wed, 12 Feb 2014 17:54:25 GMT

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

Re: data transformation question

userid128223 — Mon, 17 Mar 2014 17:58:15 GMT

Thanks Jamie

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

Re: data transformation question

userid128223 — Mon, 17 Mar 2014 18:51:37 GMT

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.

Re: data transformation question

jaimeaguilar — Tue, 18 Mar 2014 15:27:05 GMT

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

thanks