topic Re: Loop through subfields in Script in QlikView

Loop through subfields in Script

Mon, 10 Dec 2012 12:36:49 GMT

I have created an ODBC link to one of our tables from our accounting system.

I have 3 fields in this table:

-year

-entity

-amount (this field consists of 12 subfields for each month. The months are separated by '; ' ).

How do i update this table so it has 4 fields

- year

- entity

- month

- amount

I know how to separate the amount - subfield(amount, ";" ), but i don't know how to create a new field for month. That is, the first subfield will have a month of January, the second subfield will have a month of February etc.

Your help will be greatly appreciated.

Regards

Re: Loop through subfields in Script

Gysbert_Wassenaar — Mon, 10 Dec 2012 14:11:36 GMT

I'm afraid you'll have to write out the subfield expressions for the months. Luckily there's only twelve and coopy/paste helps too. Try:

Table1:

Crosstable(Month,Amount,2)

load

Entity, Year,

subfield(Amount,';',1) as Jan,

subfield(Amount,';',2) as Feb,

...etc

subfield(Amount,';',12) as Dec

from ...etc ;

Re: Loop through subfields in Script

Thu, 13 Dec 2012 22:30:18 GMT

Thanks Gysbert. I had not considered the Crosstable function .

Re: Loop through subfields in Script

CELAMBARASAN — Thu, 13 Dec 2012 22:38:33 GMT

You can also go with this

Load

Entity, Year,

Pick(iterno(), 'Jan','Feb', 'Mar', 'Apr', 'May',.......'Dec') AS Month// or Iterno AS MonthNo

SubField(Amount, iterno()) AS Amount

Resident TableName

While iterno()<=12;

To use as MonthNo you need to load inline table like below

Load * Inline {

MonthNo, Month

1, Jan

2,Feb

12, Dec];

Hope it helps