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.
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.
comm111047.qvw 153.0 K
I looked at QVW and bit confused , can we do the same by the following lines ?
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
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
min(ORD_YEAR) as MinYear,
max(ORD_YEAR) as MaxYear
LOAD * INLINE [
for i=peek('MinYear',0,'MinMax') to peek('MaxYear',0,'MinMax')
// $(i) as Year,
// SUM(Amount) as Amount
SUM(Amount) as $(i)
where ORD_YEAR = $(i)
group by CUST_ID, $(i);
drop table ORDER, MinMax;
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.
Ah, I missed that you had unaggregated detail records. Just sum those up first:
LOAD CUST_ID, ORD_YEAR, sum(Amount)
Group by CUST_ID, ORD_YEAR;
See attached qvw.
comm111047.qvw 153.5 K