Loop through subfields in Script

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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 ;

[#3]

Thanks Gysbert.  I had not considered the Crosstable function .

[CELAMBARASAN]

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