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?
Do you have to do it in the script? If not, see attached.
And if you need a script based solution you can use a generic load. Optionally joining the resulting tables into one table. This can cause performance problems on large datasets. See attached qvw.
Thanks , But I need a resident table. I need to do some analysis on accounts and I am creating a summary table.
At the end I need to do an export of 300-400K records to CSV with 10-15 dimension and few facts. I am getting a out of memory error if I do a pivot table . So I thought I will create a straight table with pre-calculated facts.
One more version... But I added multiple conditional expressions.
Thanks Gysbert! The only thing is I need only 1 record per CUST_ID
Micheal,
I looked at QVW and bit confused , can we do the same by the following lines ?
[ORD_SUM]:
NoConcatenate load CUST_ID ,ORD_YEAR as Year, SUM(Amount) as Amount Resident ORDER group by CUST_ID ,ORD_YEAR;
drop table ORDER;
What i am trying to do is get 1 record by CUST_ID , then years as column
Thanks,
Aji
Ah, I missed that you had unaggregated detail records. Just sum those up first:
Temp:
Generic
LOAD CUST_ID, ORD_YEAR, sum(Amount)
Group by CUST_ID, ORD_YEAR;
Load ...etc
See attached qvw.
Yes, it is the same thing.
(I tried to "fix" your original script and got a more complex than it needs to be )
I assume you want the complete table image in the data model. That means it will be a table box on the front end, not a straight table. Correct?
Still the question remains how dynamically add the columns, even in the table box. So, I think the straight table with conditional expression is a working solution.
Let's see what Gysbert has...
Actually your script helped. Made little change an look like working..
Please see below
MinMax:
LOAD
min(ORD_YEAR) as MinYear,
max(ORD_YEAR) as MaxYear
RESIDENT ORDER;
ORD_SUM:
LOAD * INLINE [
CUST_ID];
for i=peek('MinYear',0,'MinMax') to peek('MaxYear',0,'MinMax')
join (ORD_SUM)
load
CUST_ID,
// $(i) as Year,
// SUM(Amount) as Amount
SUM(Amount) as $(i)
Resident ORDER
where ORD_YEAR = $(i)
group by CUST_ID, $(i);
next
drop table ORDER, MinMax;