Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table which contains accounting notes, whith the following fields: CreditAccount, DebitAccount, CreditAmount, CreditAmount and I need a report that can show the credit and debit amount for any account. I need to make only one dimension based on CreditAccount and DebitAccound and it must be selectable.
Do you know how to solve this?
Thank you!
Hi Simona,
Please provide sample data and expected output then we will guide more closely.
Thanks,
Arvind Patil
Hi,
The input table is like that:
CreditAccount | DebitAccount | CreditAmount | DebitAmount |
X | Y | 100 | 0 |
Y | Z | 0 | 99 |
And the needed output:
Account | CreditAmount | DebitAmount |
Y | 100 | 99 |
Thank you!
Hi Simona,
What is the logic for calculation means what scenario you considered for this:
Account | CreditAmount | DebitAmount |
Y | 100 | 99 |
Thanks,
Arvind Patil
Hi Simona,
Shouldn't your output be
Account | CreditAmount | DebitAmount |
---|---|---|
X | 100 | |
Y | 0 | 0 |
Z | 99 |
?
Regards
Andrew
Hi,
As Andrew said your output (For this example) would be as he has put. Is that what you intended?
1 way of doing this would be to create 2 tables in qlikview based on the data that you have i.e. load credit account and credit amount in 1 table and debit account and debit amount in the second table.
The you could create a link between Credit Account and Debit account as "Account". I have represented it inline in the attached app.
Table1:
LOAD
CA as %Key_Account,
CreditAmount;
LOAD * Inline
[
CA,DA,CreditAmount,DebitAmount
X,Y,100,0
Y,Z,0,99
];
Table2:
LOAD
DA as %Key_Account,
DebitAmount;
LOAD * Inline
[
CA,DA,CreditAmount,DebitAmount
X,Y,100,0
Y,Z,0,99
];