Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following table ORDER
CUST_ID | ORD_YEAR | Amount |
A | 2008 | 10 |
A | 2008 | 15 |
A | 2009 | 15 |
A | 2010 | 20 |
A | 2012 | 5 |
A | 2014 | 10 |
A | 2014 | 20 |
B | 2008 | 10 |
B | 2008 | 5 |
B | 2008 | 25 |
B | 2009 | 30 |
B | 2013 | 5 |
B | 2013 | 10 |
B | 2014 | 5 |
Would like to create a summary table as shown below ORD_SUMM
CUST_ID | 2008 | 2009 | 2010 | 2012 | 2013 | 2014 |
A | 25 | 15 | 20 | 5 | 30 | |
B | 40 | 30 | 15 | 5 |
Tried the for loop but its throwing error.
Let vYRVal = min(ORD_YEAR);
for i=min(ORD_YEAR) to year(Today(0))
ORD_SUMM:
load
CUST_ID,
SUM(Amount) as $(vYRVal)
Resident
ORDER
where
ORD_YEAR = $(vYRVal)
group by
CUST_ID;
vYRVal = $(vYRVal)+1;
next
drop table ORDER;
Any Idea on how to do this?
Thanks Gysbert!
Still the back end data has multiple records
Makes sense.
(BTW, you don't need to group by $(i) anymore, although no harm either.)
The only remaining question is what to do with the dynamic front end.
That helps even better for 2011 there were no data and now I have blank column for that .
This is mostly 1 time deal. But I have similar reports where I dont use year as column name rather the difference between the current year and order year prefixed with YR as column name and the title for YR_0 ( which is 2014) will be year(Today()) and for YR_1 (i.e 2013) will be (year(Today())-1) and so forth. The table is limited to past 5 years.
Are you sure you opened the correct version? Perhaps saving it under a different name helps.
Sorry, I was looking at the old one..