Help on Column to Row

[#1]

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

[#2]

But this doesnt meet the requirement

[anbu1984]

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;