10 Replies Latest reply: Nov 5, 2014 7:51 AM by anbu cheliyan

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

• Re: Help on Column to Row

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)

*

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

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

Temp:

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

Hi Anbu,

Thank You.

I will check and will let you know.

• Re: Help on Column to Row

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

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

• Re: Help on Column to Row

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

How to handle n rows

• Re: Help on Column to Row

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

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:

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;

• Re: Help on Column to Row

See this

Tab1:

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)

Product , Tab1 ,  Tab2   ,Tab3 ,  Tab4

Resident Tab1;

//DROP table  Tab1;

Tab3:

CrossTable(Col,X,1)