Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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)
See the attached.
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
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
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
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.