Create table columns with the names stored in other table.

[juan_c_martinez]

Is there any way to transfrorm Table 1 into Table 2 ?

( We don't know perviosusly how many differnt measure can be)

Table1

ID    Measure     Value

1     Sales          10

2     Cost            5

3     Margin         50

Table2

ID     Measure    Sales     Cost     Margin    

1      Sales         10           -          -

2      Cost            -           5          -

3      Margin        -             -         50

Thanks in advance

Juan C.

[hic]

"Generic Load" does exactly this. Hence

Generic Load ID, Measure, Value ....

HIC

[juan_c_martinez]

HIC,

Thanks!! But with generic load are created tree tables (table2.sales, table2.cost, table2.margin linked by id field ) the result I look for is a unique table  expandable in columns according with the values existing in the field measure of table1

Juan C.

[hic · Accepted solution]

For your analysis it shouldn't make any difference if the data is in one table or in several.

But if you still want it in one table, you can do the following:

Measures:

Load distinct Measure From Data.qvd (qvd);

Data:

Load distinct ID From Data.qvd (qvd);

For vMeasureNo = 0 to NoOfRows('Measures') - 1

  Let vMeasure = Peek('Measure',vMeasureNo,'Measures');

  Left Join (Data)

  Load ID, Value as [$(vMeasure)] From Data.qvd (qvd);

Next vMeasureNo

Drop Table Measures;

HIC

[juan_c_martinez]

Hic,

Your solution works fine, thaks.  I have done other that also works ( avoiding woks with joins):

To experimtent a litle bit more I have done it using CONCAT function to create a list in order to operate with FOR EACH. At the beginig it doesn't work but after some cosmetic changes (in yellow) it does:

Thanks fr your time

Juan C.