Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
On our database we have a sales achieve file which holds sales by month for the last 3 years.
I have written a LOAD statement that pick these fields out and concatenates them into a single table eg.
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS01 as [Sales value],
$(vYear) as year,
dual('Jan',1) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS01 FROM ....SAHACCPF;
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS02 as [Sales value],
$(vYear) as year,
dual('Feb',2) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS02 FROM ....SAHACCPF;
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS03 as [Sales value],
$(vYear) as year,
dual('Mar',3) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS03 FROM ....SAHACCPF;
...etc etc...
I know you can use a For..Next loop in the script, but can I use it in a Field name ie "SAAS0" & n
Thanks
Oli
Hi Oli,
Yes, you can use it with Dollar Sign Expansion.
i.e
For i=1 to 9
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS0$(i) as [Sales value],
$(vYear) as year,
dual('Jan',1) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS0$(i) FROM ....SAHACCPF;
Next
or
For Each i in '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS$(i) as [Sales value],
$(vYear) as year,
dual('Jan',1) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;
Next i
This will go through SAAS01, SAAS02, SAAS03 ... SAAS12.
You may need to work on the year and month logic to make it generic.
For Each vYear in ....
For Each i in '01' ... '12'
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS$(i) as [Sales value],
$(vYear) as year,
//dual('Jan',1) as month
if(i = '01',
dual('Jan',1),
if(i ='02',
dual('Feb',2)
...
)) as month,
Dual(Date('$(i)/01/$(vYear)','MMM'), num($(i))) as month2
;
SQL
SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;
Next i
Next vYear
Hopefully this is what you needed.
Sean
Hi Oli,
Yes, you can use it with Dollar Sign Expansion.
i.e
For i=1 to 9
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS0$(i) as [Sales value],
$(vYear) as year,
dual('Jan',1) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS0$(i) FROM ....SAHACCPF;
Next
or
For Each i in '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS$(i) as [Sales value],
$(vYear) as year,
dual('Jan',1) as month
;
SQL
SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;
Next i
This will go through SAAS01, SAAS02, SAAS03 ... SAAS12.
You may need to work on the year and month logic to make it generic.
For Each vYear in ....
For Each i in '01' ... '12'
SalesHistoryByAcc:
LOAD
SAAACN & '-' & SAABRN as %Acc,
SAAS$(i) as [Sales value],
$(vYear) as year,
//dual('Jan',1) as month
if(i = '01',
dual('Jan',1),
if(i ='02',
dual('Feb',2)
...
)) as month,
Dual(Date('$(i)/01/$(vYear)','MMM'), num($(i))) as month2
;
SQL
SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;
Next i
Next vYear
Hopefully this is what you needed.
Sean
Thanks Sean
I guessed there was a better less verbose method.
Oli