Two fields, one dimension

[simonagheo]

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!

[arvind_patil]

Hi Simona,

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

Thanks,

Arvind Patil

[simonagheo]

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!

[arvind_patil]

Hi Simona,

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

Account	CreditAmount	DebitAmount
Y	100	99

Thanks,

Arvind Patil

[effinty2112]

Hi Simona,

Shouldn't your output be

Account	CreditAmount	DebitAmount
X	100
Y	0	0
Z		99

?

Regards

Andrew

[Anonymous]

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

];