Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Loop through subfields in Script

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
3 Replies
MVP & Luminary
MVP & Luminary

Re: Loop through subfields in Script

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
Not applicable

Re: Loop through subfields in Script

Thanks Gysbert.  I had not considered the Crosstable function .

Re: Loop through subfields in Script

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

Community Browser