QlikView load script and table relationships - using old sql conditions script

Mon, 12 Jul 2010 12:38:58 GMT

Hi i have 3 tables that i want to import and join based on a previous SQL script. What would the SQL script below have to look like in QlikView load script as i know the QlikView syntax is different?

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

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.

My Qlikview Script below is what ive produced and it seems to be working except no transaction dates are returned .

----------

ACCOUNTS:
Load
ACC_ID,
ACCSHRT_COD,
ACC_COD1 as [Account Code],
SERVAL_ID,
SUBLED_ID as [Ledger Type],
ACC_DSC as [Account Description],
LEDTYP_ID WHERE LEDTYP_ID = '01' AND left(SUBLED_ID,2)='AR';
SQL SELECT * FROM ALI.ACCOUNT;
STORE ACCOUNTS INTO [ACCOUNTS.qvd];

TRANSACTIONS:
Load
TRX_ID,
TRXTYP_ID as [Transaction Type],
TRX_REF as [Invoice Reference],
BASE_AMT,
DRCR_FLG,
DRCR_FLG * OSBASE_AMT as [Outstanding Amount],
OSDRCR_FLG,
ACC_ID
where exists(ACC_ID,ACC_ID)and OSBASE_AMT <>'0.00';
SQL SELECT * FROM ALI.TRX;
STORE TRANSACTIONS INTO [TRANSACTIONS.qvd];

TRANSACTION_DATES:
Load
SRT_DAT as [Sort Date],
SRT_DAT as DAYS,
DATTYP_ID,
TRX_DAT,
TRX_ID
WHERE exists(TRX_ID,TRX_ID) and DATTYP_ID = 'DUE';
SQL SELECT * FROM ALI.TRXDAT;
STORE TRANSACTION_DATES INTO [TRANSACTION_DATES.qvd];

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

Regards

Jason

topic QlikView load script and table relationships - using old sql conditions script in QlikView

QlikView load script and table relationships - using old sql conditions script