Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Loop through subfields in Script

Hi

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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 ;


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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 ;


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Not applicable

Thanks Gysbert.  I had not considered the Crosstable function .

Highlighted

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