topic Re: Help on Column to Row in QlikView

Help on Column to Row

Wed, 05 Nov 2014 10:22:51 GMT

Hi All,

I have date in the following way.

Product Time1 Time2 Emx Loc Value Tab1 Tab2 Tab3 Tab4 Col1 COl2 Col3

X 20-3-14 14-03-10 ABC 12 30 A B C DD 1 2 3

Y 20-3-14 14-03-10 ABC 12 40 D B C DD 7 4 5

Key can be formed on Product ,Time1 ,Time2 , Emx ,Loc.

Now I need Output with new Columns : Number, Tab. Col

Product Time1 Time2 Emx Loc Value Number Tab Col

X 20-3-14 14-03-10 ABC 12 30 1 A 1

X 20-3-14 14-03-10 ABC 12 30 2 B 2

X 20-3-14 14-03-10 ABC 12 30 3 C 3.

Y 20-3-14 14-03-10 ABC 12 40 1 D 7

Y 20-3-14 14-03-10 ABC 12 40 2 B 4

Y 20-3-14 14-03-10 ABC 12 40 3 C 5

So basically the values in Number field should be based on number of Tab's and COls. In my case I have three 3 Tabs and 3 Cols(Tab1, Tab2, Tab3) are there for all records so Number should be 1 2 3 .

Can anyone help me on this.

BR,

Neehu

Re: Help on Column to Row

sujeetsingh — Wed, 05 Nov 2014 10:31:45 GMT

I think this will do for you

Cross tables are table where facts are divided on the basis of dimension values.

for example we have sales for every month. then, if in our database we load sales amounts as a separated field for every month as

Product Jan Feb Mar etc

a 100 200 300

So, basically we have 1 single fact as sales amount here, and jan-dec are dimension memebers of month dimension but we are creating different fields for every dimension member and treating them as different field.

Loading data this way, makes it difficult to properly aggregrate the data over different dimensions and difficult to do analysis.

So, we convert this crosstable into a normal table.

using

crosstable (Month, Amount)

load

from a.qvd(qvd);

then we will get the same table as

Product Month Amount

a jan 100

a feb 200

a mar 300

I hope you understand now, what is crosstable. let me know if it solves your concern.

Regards,

Sujeet

Re: Help on Column to Row

anbu1984 — Wed, 05 Nov 2014 10:33:51 GMT

Temp:

Load * Inline [

Product,Time1,Time2, Emx, Loc,Value, Tab1,Tab2,Tab3,Tab4,Col1,COl2,Col3

X,20-3-14,14-03-10,ABC, 12,30, A, B, C, DD,1, 2, 3

Y, 20-3-14,14-03-10,ABC, 12,40,D, B, C, DD,7, 4, 5 ];

Temp1:

Load Product,Time1,Time2, Emx, Loc,Value,Tab1 As Tab,Col1 As Col Resident Temp;

Concatenate

Load Product,Time1,Time2, Emx, Loc,Value,Tab2 As Tab,COl2 As Col Resident Temp;

Concatenate

Load Product,Time1,Time2, Emx, Loc,Value,Tab3 As Tab,Col3 As Col Resident Temp;

NoConcatenate

Load *,AutoNumber(RecNo(), Product&'_'&Time1&'_'&Time2&'_'& Emx&'_'& Loc&'_'&Value) As Number Resident Temp1;

Drop Tables Temp,Temp1;

Re: Help on Column to Row

Wed, 05 Nov 2014 10:40:57 GMT

Hi Sujjeth,

ThankYou.

But if you see my requirement it is different. Because few columns should come under one field and few should come in another filed.

Re: Help on Column to Row

Wed, 05 Nov 2014 10:43:17 GMT

Hi Anbu,

Thank You.

I will check and will let you know.

Re: Help on Column to Row

Wed, 05 Nov 2014 10:47:47 GMT

But if My Tab and Col increases to Tab10 and COl10 then Should I write code(10 times concatenate) as per your above code?

Will that be a fine?

Can you suggest me some script which checks dynamically and will do the needful without concatenating everytime.

Re: Help on Column to Row

anbu1984 — Wed, 05 Nov 2014 11:23:19 GMT

If number of columns(Tab10 and COl10) won't change, then you can use 10 concatenate

Re: Help on Column to Row

Wed, 05 Nov 2014 11:36:35 GMT

Ok. May i know why you have used Autonumber?

Because Autonumber will give random number. But i want 1 2 3 ,.... in Number field

Re: Help on Column to Row

sujeetsingh — Wed, 05 Nov 2014 12:08:31 GMT

How to handle n rows

Re: Help on Column to Row

sujeetsingh — Wed, 05 Nov 2014 12:08:45 GMT

See this

Tab1:

LOAD * INLINE [

Product , Time1, Time2 , Emx , Loc , Value , Tab1 , Tab2 ,Tab3 , Tab4 ,Col1 , COl2 , Col3

X , 20-3-14 , 14-03-10 , ABC , 12 , 30 , A , B , C , DD , 1 , 2 , 3

Y , 20-3-14 , 14-03-10 , ABC , , 12 , 40 , D , B , C , DD , 7 , 4 , 5

];

Tab2:

CrossTable(Tab,Y,1)

LOAD

Product , Tab1 , Tab2 ,Tab3 , Tab4

Resident Tab1;

//DROP table Tab1;

Tab3:

CrossTable(Col,X,1)

LOAD

Product, Col1 , COl2 , Col3

Resident Tab1;

DROP table Tab1;

Re: Help on Column to Row

Wed, 05 Nov 2014 12:51:01 GMT

But this doesnt meet the requirement

Re: Help on Column to Row

anbu1984 — Wed, 05 Nov 2014 12:51:01 GMT

Autonumber(RecNo(),RecNo()) along with RecNo() gives unique running number from 1 for each key. Check the result again, it gives correct result.

LOad *,AutoNumber(RecNo(),Key) Inline [

Key,Data

1,11

1,22

2,33

3,44

3,55 ];

You can also try like this, provided your column names follow naming convention like Tab[Number] and Col[Number]

Temp:

Load * Inline [

Product,Time1,Time2, Emx, Loc,Value, Tab1,Tab2,Tab3,Col1,Col2,Col3

X,20-3-14,14-03-10,ABC, 12,30, A, B, C, 1, 2, 3

Y, 20-3-14,14-03-10,ABC, 12,40,D, B, C, 7, 4, 5 ];

Set vListOfColumns = ;

For vColNo = 1 to NoOfFields('Temp')

Let vColName = FieldName($(vColNo),'Temp') ;

If '$(vColName)' Like 'Tab*' Then

Let vNo = PurgeChar('$(vColName)','Tab');

Temp1:

Load Product,Time1,Time2, Emx, Loc,Value,Tab$(vNo) As Tab,Col$(vNo) As Col Resident Temp;

ENDIF

Next vColNo

NoConcatenate

Load *,AutoNumber(RecNo(), Product&'_'&Time1&'_'&Time2&'_'& Emx&'_'& Loc&'_'&Value) As Number Resident Temp1;

Drop Tables Temp,Temp1;