topic Two fields, one dimension in QlikView

Two fields, one dimension

simonagheo — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Two fields, one dimension

arvind_patil — Mon, 06 Nov 2017 10:05:25 GMT

Hi Simona,

Please provide sample data and expected output then we will guide more closely.

Thanks,

Arvind Patil

Re: Two fields, one dimension

simonagheo — Mon, 06 Nov 2017 10:37:22 GMT

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!

Re: Two fields, one dimension

arvind_patil — Tue, 07 Nov 2017 09:56:42 GMT

Hi Simona,

What is the logic for calculation means what scenario you considered for this:

Account	CreditAmount	DebitAmount
Y	100	99

Thanks,

Arvind Patil

Re: Two fields, one dimension

effinty2112 — Tue, 07 Nov 2017 10:06:12 GMT

Hi Simona,

Shouldn't your output be

Account	CreditAmount	DebitAmount
X	100
Y	0	0
Z		99

Regards

Andrew

Re: Two fields, one dimension

Anonymous — Tue, 07 Nov 2017 12:15:03 GMT

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

];