Discussion Board for collaboration on QlikView Scripting.
I have the following table ORDER
Would like to create a summary table as shown below ORD_SUMM
Tried the for loop but its throwing error.
Let vYRVal = min(ORD_YEAR);
for i=min(ORD_YEAR) to year(Today(0))
SUM(Amount) as $(vYRVal)
ORD_YEAR = $(vYRVal)
vYRVal = $(vYRVal)+1;
drop table ORDER;
Any Idea on how to do this?
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.
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
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.
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;