Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am following the document Generating Missing Data In QlikView, the Example: Accumulate a number section.
I have Table1:
There can be multiple transactions with different Amounts within one Date/Co/CashFlow combination.
Date | Company | Account | CashFlow |
---|---|---|---|
I need to:
- aggregate the table
- add accumulated CashFlow field
Below is my script (apart from master calendar and other misc code):
TempTable_Data:
Load
Company
Account
Date,
CashFlow
Resident MainTable; //I derive all values from the MainTable, which I will subsequently drop
//Aggregate table - I want to group all CashFlows
TempTable_Data2:
Load
Company,
Account,
Date,
Sum([CashFlow]) as CashFlow
Resident TempTable_Data
Group By Company, Account, Date;
Drop Table TempTable_Data;
//As per the instruction from the link below
NewTable:
Load
Company,
Account,
Date,
If( Account=Peek(Account), // if the Account is the same as in the previous row
RangeSum([CashFlow],Peek([AccCashFlow)),
RangeSum([CashFlow]))
as AccCashFlow
Resident TempTable_Data2 ??? Here I receive error that table TempTable_Data2 not found
Order By Company, Account, Date;
Drop Table TempTable_Data2 ;
Drop Table MainTable ;
TempTable_Data2
will be concatenated implicitly to TempTable_Data because they share 100% identical field names...
try placing
noconcatenate
between the loads.
TempTable_Data2
will be concatenated implicitly to TempTable_Data because they share 100% identical field names...
I should have asked at start
Spent half of the day playing with the script )
At least I mastered it.
Thank you!
meanwhile I write concatenate / noconcatenate explicitly between every load.