Calculate Field Name in Script

[Anonymous]

Hi,

I have a question, I have a table in DB with many field that have this name:

Table1:

ItemID01

ItemID02

ItemID03

...

Quantity01

Quantity02

Quantity03

...

I need to load every Item field anf quantity with the same field name.

I try with this:

For i=1 to 3

Temp:

LOAD 'ItemID' + text(num(i,'00')) AS ItemID.

   'Quantity' + text(num(i,'00')) AS Quantity

    FROM Table1;

but seem that QlikView dont resolve names of field 'ItemIDxx and Quantityxx.

SO I dont know wich is the right syntax.

Please can you help me?

Thank you very much.

[jsanchezh]

Table1:

LOAD * INLINE [

    IDKEY, ItemID01, ItemID02, ItemID03, Quantity01, Quantity02, Quantity03

    1, A, B, C, 5, 10, 8

    2, E, A, D, 8, 1, 9

];

final_table:

LOAD IDKEY,

  ItemID01 as ItemID,

  Quantity01 as Quantity

  Resident Table1;

concatenate ('final_table')LOAD IDKEY,

  ItemID02 as ItemID,

  Quantity02 as Quantity

  Resident Table1;

concatenate ('final_table') LOAD IDKEY,

  ItemID03 as ItemID,

  Quantity03 as Quantity

  Resident Table1;

drop Table Table1;

[Anonymous]

Hi,

now it work!

For i=1 to 3

LET vItem =  'ItemID' & text(num(i,'00'));

LET vQuantity =  'Quantity' & text(num(i,'00'));

Temp:

LOAD $(vItem) AS ItemID.

   $(vQuantity') AS Quantity

    FROM Table1;

[anbu1984]

Itm_Temp:
CrossTable(ItemID,ItemValue,1)
Load IDKEY,ItemID01,ItemID02,ItemID03 Inline [
IDKEY,ItemID01,ItemID02,ItemID03,Quantity01,Quantity02,Quantity03
1, A, B,C,5,10,8
2, E, A,D,8,1,9 ];


Itm_Final:
Load *,PurgeChar(ItemID,'ItemID') As Ord Resident Itm_Temp;


Qty_Temp:
CrossTable(Qty,QtyValue,1)
Load IDKEY,Quantity01,Quantity02,Quantity03 Inline [
IDKEY,ItemID01,ItemID02,ItemID03,Quantity01,Quantity02,Quantity03
1, A, B,C,5,10,8
2, E, A,D,8,1,9 ];


Join(Itm_Final)
Load *,PurgeChar(Qty,'Quantity') As Ord Resident Qty_Temp;


Drop Tables Itm_Temp,Qty_Temp;

[#5]

Hi,

Try like below,

Hopefully your issue will resolve,

A:

load * inline [

   ItemID01, ItemID02, ItemID03

    1, 2, 3

];

Let vMax = right('ItemID03',2);

Let vMin = right('ItemID01',2);

for i='$(vMin)' to '$(vMax)'

Let vi = text(num($(i),'00'));

B:

Load

     ItemID$(vi) as ItemID

resident A;

NEXT

drop table A;