qlikview syntax - loading qvd files and table left join

[#1]

I have created 2 qvd files and and now want to import them into my qlikivew application and perform a left join.

The accounts qvd table has 1130 rows and the transactions table 161000 rows. Therefore i want to add a left join to show all ACCOUNTS and only those TRANSACTIONS where ACC_ID match.

My script is the following

LOAD * FROM data\ACCOUNTS.qvd(qvd);
LOAD * FROM data\TRANSACTIONS.qvd(qvd);

FINALTABLE:
SELECT Accounts.ACC_ID, Transactions.ACC_ID
FROM Accounts left join Transactions

Why doesnt this script work?

The load part works but the FINALTABLE doesnt.

Regards

Jason

[#2 · Accepted solution]

Jason,

As I understand the problem you are trying to solve is to load only those accounts that have one ro more transactions?

Try this script:
transactions:
LOAD * FROM data\TRANSACTIONS.qvd(qvd);

LOAD * FROM data\ACCOUNTS.qvd(qvd)
where exists(ACC_ID,ACC_ID);

After the above lines you will have two tables in Qlikview, associated by ACC_ID, and only those Accounts loaded that have at least one transaction.

Karol

[#3]

Hi thanks for your answer, that seemed to work fine.

However i was also wondering if we could take this a little further and get my whole data load script fixed.

The original SQL script looked like the following:

-------------------------------------

SELECT
ACCOUNT.ACC_ID,
ACCOUNT.ACCSHRT_COD,
ACCOUNT.ACC_COD1,
ACCOUNT.SERVAL_ID,
ACCOUNT.SUBLED_ID,
ACCOUNT.ACC_DSC,
TRX.TRX_ID,
TRX.TRXTYR_ID,
TRX.BASE_AMT,
TRX.DRCR_FLG,
TRX.OSBASE_AMT,
TRX.OSDRCR_FLG,
TRXDAT.SRT_DAT,
TRXDAT.DATTYP_ID,
TRXDAT.TRX_DAT,
TRX.TRX_REF

FROM ATE.ACCOUNT ACCOUNT, ATE.TRX TRX, ATE.TRXDAT TRXDAT

WHERE ACCOUNT.ACC_ID = TRX.ACC_ID AND TRX.TRX_ID = TRXDAT.TRX_ID
AND ((ACCOUNT.LEDTYP_ID='01' AND (TRX.OSBASE_AMT<>0.00) AND (TRXDAT.DATTYP_ID='DUE'))

--------------------------------

It would be great to simply add this SQL script to QlikView data script load but i know it doesnt work like that. Therefore what would the script above have to look like in QlikView load script to get the same outcome?

Note the 3 tables and their relationships + the conditions with the AND + WHERE statements.

Regards

Jason