Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone
I have a doubt of which one is the best option to build a Statement from a list of bank records.
Let's say I have
Name Bank Date Amount
Deutsche Bank 31/12/2015 200
Deutsche Bank 07/01/2016 -100
Deutsche Bank 15/03/2016 50
Deutsche Bank 29/03/2016 -120
So my question Should I first calculate the statement in the set analysis for every day of the calendar, store the data and reload so I can use this new calculate field to have the right statement
Or I can calculate the statement in the Pivot Table?
What I want is that for each bank and every day of the calendar the amount show is the sum of the previous records.
Ex: Deutsche Bank 01/01/2016 = 200
31/01/2016 = 100
20/02/2016 =100
31/03/2015 = 30
30/06/2016 = 30
Thanks in advance
f
an idea in the attachment, script is
B:
load * inline [
Name Bank, Date, Amount
Deutsche Bank, 31/12/2015, 200
Deutsche Bank, 07/01/2016, -100
Deutsche Bank, 15/03/2016, 50
Deutsche Bank, 29/03/2016, -120
Bank, 05/01/2016, 2000
Bank, 07/01/2016, -100
Bank, 15/03/2016, 50
Bank, 29/03/2016, -120
];
C:
load date(makedate(2015,12,31) + rowno()-1) as Date AutoGenerate 366;
join (C) LOAD Distinct [Name Bank]
Resident B;
Left Join (C) LOAD * Resident B;
DROP Table B;
FINAL:
LOAD
[Name Bank],
Date,
If([Name Bank]=Peek('Name Bank'), Alt(Amount,0) + Peek('CumAmount'), Alt(Amount,0)) as CumAmount,
Amount
Resident C
Order By [Name Bank], Date;
DROP Table C;
Fabio,
Is this possible to share the App
As i know, it is possible in Oracle Trigger command. If you are the oracle developer please create one trigger for that and then call that trigger to Qlik. Might be helpful
- ANIL
You could solve such a case by creating a time-period with From - To per interrecord-functions like Peek() or Previous() ? and afterwards you populate the date with a while-loop.
t1:
load Bank, Date as ToDate, previous(Date) as FormDate, Amount
resident YourFirstLoadOfTheseData order by Bank, Date desc;
t2:
load Bank, date(FromDate + iterno() - 1) as Date, Amount
Resident t1 while FromDate + iterno() - 1 <= num(ToDate);
- Marcus
an idea in the attachment, script is
B:
load * inline [
Name Bank, Date, Amount
Deutsche Bank, 31/12/2015, 200
Deutsche Bank, 07/01/2016, -100
Deutsche Bank, 15/03/2016, 50
Deutsche Bank, 29/03/2016, -120
Bank, 05/01/2016, 2000
Bank, 07/01/2016, -100
Bank, 15/03/2016, 50
Bank, 29/03/2016, -120
];
C:
load date(makedate(2015,12,31) + rowno()-1) as Date AutoGenerate 366;
join (C) LOAD Distinct [Name Bank]
Resident B;
Left Join (C) LOAD * Resident B;
DROP Table B;
FINAL:
LOAD
[Name Bank],
Date,
If([Name Bank]=Peek('Name Bank'), Alt(Amount,0) + Peek('CumAmount'), Alt(Amount,0)) as CumAmount,
Amount
Resident C
Order By [Name Bank], Date;
DROP Table C;
Very Helpful.
Thanks a lot!!
Sorry Mate
that's perfect if only a have 1 Amount in each date.
But in a case I have more than one in the selected date it show me the different CumAmount and not the total Amount. Any idea how to solve it?
post a small example (.qvw)
HI
I don't know why the attachment button it doesn't show
That's the script I'm using
B:
LOAD Conto,
dt_reg,
[Nr Reg],
[Imp Ope],
[Descr Top],
F6
FROM
(biff, embedded labels, table is [Elenco movimenti_2015$]);
C:
load date(makedate(2014,12,31) + rowno()-1) as dt_reg AutoGenerate 366;
join (C) LOAD Distinct [Conto]
Resident B;
Left Join (C) LOAD * Resident B;
DROP Table B;
FINAL:
LOAD
[Conto],
date(date# ([dt_reg],'DD/MM/YYYY'), 'DD-MM-YYYY') AS dt_reg,
If([Conto]=Peek('Conto'), Alt([Imp Ope],0) + Peek('Saldo'), Alt([Imp Ope],0)) as Saldo,
[Imp Ope],
[Descr Top]
Resident C
Order By [Conto], dt_reg;
DROP Table C;
And this is the table
The 3rd of January it doesn't show anything in a pivot table and two option on the Saldo table.
Conto | dt_reg | Nr Reg | Imp Ope | Descr Top | |
DEUTSCHE | 03/01/2015 | 0000018044 | 1384,73 | INCOME | |
DEUTSCHE | 03/01/2015 | 0000018746 | -0,8 | EXPENSE | |
DEUTSCHE | 02/04/2015 | 0000018747 | 3259,2 | INCOME | |
DEUTSCHE | 31/12/2014 | 0000018738 | -12679,08 | SDD |
try the attachement, always with test data of my previous post