topic Re: How to I link two rows within the same QVD ? in QlikView

How to I link two rows within the same QVD ?

Wed, 27 Nov 2013 09:55:48 GMT

Within a Transactions QVD I have the following fields:

Account Number

Transaction Document Number

Transaction Amount

Transaction Posting Date

Transaction Clearing Date

Transaction Clearing Document Number

In certain scenarios, a transaction will have a 'Transaction Clearing Document Number' which is equal to the 'Transaction Document Number' of an entirely different row. I need to extract these scenarios so that they are displayed on a single row rather than two rows

For example:

Account 1

Account Number 123

Transaction Document Number 999

Transaction Amount £50

Transaction Posting Date 01/04/2013

Transaction Clearing Date 20/04/2013

Transaction Clearing Document Number 777

Account 2

Account Number 456

Transaction Document Number 777

Transaction Amount £50

Transaction Posting Date 20/04/2013

Transaction Clearing Date 10/06/2013

Transaction Clearing Document Number 444

As you can see, whilst the Account Numbers differ, the Transaction Document Number on Account 2 is equal to the Transaction Clearing Document Number on Account 1

Would this involve a LEFT JOIN with RESIDENT LOAD ?

Thanks

Re: How to I link two rows within the same QVD ?

Nicole-Smith — Wed, 27 Nov 2013 15:08:13 GMT

When you say they should be displayed on a single row, can you elaborate as to what that single row should look like? Without us knowing that, we are unable to help you achieve what you're looking for.

Re: How to I link two rows within the same QVD ?

Wed, 27 Nov 2013 15:58:37 GMT

The final row would only really need to be:

Source Account 123

Destination Account 456

Transaction Amount £50

But it would need to be derived from the joins I mentioned above, i.e. Clearing Document Number on source account (account 1) = Document Number on destination account (account 2)

Re: Re: How to I link two rows within the same QVD ?

Nicole-Smith — Wed, 27 Nov 2013 16:36:11 GMT

See the attached.

Re: How to I link two rows within the same QVD ?

arbernardez_old — Wed, 27 Nov 2013 16:41:06 GMT

Hello Chris,

why don't you create another table with fields you need to join from this Transaction table and linking these tables changing the name of the link field. I mean,

you can do something like follows:

TRX:

load

Account Number , //123

Transaction Document Number , //999

Transaction Amount , //£50

Transaction Posting Date , //01/04/2013

Transaction Clearing Date , //20/04/2013

Transaction Clearing Document Number , //777

Transaction Clearing Document Number as TRX_ID //777

from Transaction.QVD

left keep (TRX)

CLEARING_DOCS:

load

Account Number as CD.Account Number , // 456

Transaction Document Number as TRX_ID , //777

Transaction Document Number as CD.Account Number , //777

Transaction Amount as CD.Account Number, //£50

Transaction Posting Date as CD.Account Number , //20/04/2013

Transaction Clearing Date as CD.Account Number, //10/06/2013

Transaction Clearing Document Number as CD.Account Number , //444

from Transaction.QVD

note that you may rename fields to just do the link with the TRX_ID,

if you do a left keep, then you'll keep two tables, but you can do also a left join, is up to you.

Regards,

Alberto Rodríguez

Re: How to I link two rows within the same QVD ?

VishalWaghole — Wed, 27 Nov 2013 17:25:33 GMT

Hi Chris Evans,

Try this Script,

Hope that will Help you..

ACCOUNT1:

Load [Account Number],

[Transaction Document Number],

[Transaction Amount],

[Transaction Posting Date],

[Transaction Clearing Date],

[Transaction Clearing Document Number] as [TRANSACTION_NUMBER]

From TableName;

left join(ACCOUNT1):

Load [Account Number],

[Transaction Document Number] as [TRANSACTION_NUMBER],

[Transaction Amount],

[Transaction Posting Date],

[Transaction Clearing Date],

[Transaction Clearing Document Number]

From TableName;

Thanks and Regards,

Vishal Waghole

Re: How to I link two rows within the same QVD ?

Thu, 28 Nov 2013 08:50:34 GMT

Hi all,

Thanks for all of your suggestions - really helpful.

Just one final point, the Account Numbers in the Source and Destination accounts MUST be different so how would I script this ?

When completing a LEFT JOIN I've tried WHERE SOURCE ACCOUNT <> DESTINATION ACCOUNT but Qlik doesn't like it

Re: How to I link two rows within the same QVD ?

VishalWaghole — Thu, 28 Nov 2013 09:37:38 GMT

Hi Chris,

When you are doing Left join then

you no need to use condition like SOURCE ACCOUNT <> DESTINATION ACCOUNT

Cause your Data is filter out with respective above one table. That means

Only those data will be restricted who has no any value in above table.

Thanks and Regards,

Vishal Waghole.