topic Re: Calculate Field Name in Script in QlikView

Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 09:57:39 GMT

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.

Re: Calculate Field Name in Script

sunny_talwar — Mon, 13 Apr 2015 10:30:23 GMT

Not exactly sure about what you are trying to do? Can you post a sample QV file??

Best,

Sunny

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 10:38:58 GMT

why you are not selecting field names directly from table (load wizard).

Re: Calculate Field Name in Script

jsanchezh — Mon, 13 Apr 2015 10:50:13 GMT

Hello,

I don't understand what you are tring to do either. Do they have the same values? are you traying to concatenate them?

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 10:52:15 GMT

try ampersand "&" instead of "+"

Temp:

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

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

FROM Table1;

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 11:00:59 GMT

Hi,

here QVW file.

I need to have the names of fields (for example if i is equal 2 I need to have ItemID02 and Quantity02).

I dont know if this is possible, if not is a great prblem, in database I have an arry for every record.

Thank you!

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 11:04:29 GMT

These is different field with different value and I must to work with this.

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 11:06:16 GMT

Thank you Rudolf!

But now QlikView say: "Field i dont exist".

How can I declare i?

Thank you again!

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 11:07:08 GMT

I need all fields, I need to take the array of fields and use some that was a different record...

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 11:13:27 GMT

For example:

FROM (Database Table):

Table1:

IDKEY ItemID01 ItemID02 ItemID03 Quantity01 Quantity02 Quantity03
1 A B C 5 10 8
2 E A D 8 1 9

TO:

Temp (Qlikview Table):

IDKEY ItemID Quantity

1 A 5

1 B 10

1 C 8

2 E 8

2 A 1

2 D 9

Is it impossible?

Re: Calculate Field Name in Script

jsanchezh — Mon, 13 Apr 2015 11:17:52 GMT

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;

Re: Calculate Field Name in Script

Anonymous — Mon, 13 Apr 2015 11:27:06 GMT

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;

Re: Calculate Field Name in Script

anbu1984 — Mon, 13 Apr 2015 11:29:39 GMT

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;

Re: Calculate Field Name in Script

Mon, 13 Apr 2015 11:34:05 GMT

Hi,

Try like below,

Hopefully your issue will resolve,

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'));

Load

ItemID$(vi) as ItemID

resident A;

drop table A;