Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help on Column to Row

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

11 Replies
sujeetsingh
Master III
Master III

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

anbu1984
Master III
Master III

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;

Not applicable
Author

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.

Not applicable
Author

Hi Anbu,

Thank You.

I will check and will let you know.

Not applicable
Author

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.

anbu1984
Master III
Master III

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

Not applicable
Author

Ok. May i know why you have used Autonumber?

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

sujeetsingh
Master III
Master III

How to handle n rows

sujeetsingh
Master III
Master III

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;