Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ;
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 ;
Thanks Gysbert. I had not considered the Crosstable function .
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