How to I link two rows within the same QVD ?

[#1]

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

[Nicole-Smith]

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.

[#3]

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)

[Nicole-Smith]

See the attached.

[arbernardez]

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

[VishalWaghole]

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

[#7]

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

[VishalWaghole · Accepted solution]

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.