SQL Load Statements

[danielnevitt]

Hi,

I have the below SQL load statement and I am having issues with joining the different tables together as required.

Basically I want to join the first table with the second table using TRADE_NUM and then the second table with the third table using COUNTERPARTY.

At the moment the load does a join on CO_CD for the first two tables as well and doesn't join by COUNTERPARTY.

I'm new to SQL coding, so any help with this will be much appreciated.

Thanks,

Daniel

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT32 TO [Tempest_DCF_Prod;DBQ=DCFDWP1]

//-------- Start Multiple Select Statements ------

LOAD *,
Date(CREATE_DT) AS CREATE_DATE,
Month(CREATE_DT) AS CREATE_DT_MONTH,
Year(CREATE_DT) AS CREATE_DT_YEAR,
MonthName(CREATE_DT) AS CREATE_DT_MONTHYEAR,
Date(PAY_DUE_DT) AS PAY_DUE_DATE,
Month(PAY_DUE_DT) AS PAY_DUE_DT_MONTH,
Year(PAY_DUE_DT) AS PAY_DUE_DT_YEAR,
MonthName(PAY_DUE_DT) AS PAY_DUE_DT_MONTHYEAR,
Date(VOUCHER_DT) AS VOUCHER_DATE,
Date(VOUCHER_FULLY_PAID_DT) AS VOUCHER_FULLY_PAID_DATE;
SQL SELECT "AMOUNT_DUE_IN_PAY_CURR",
"AMOUNT_DUE_IN_PRICE_CURR",
"BOOK_CD",
"CMDTY_CD",
"CO_CD",
"CO_COUNTRY_CD",
"CONTRACT_CO_CD",
"COST_CD",
"COST_CURR_CD",
"COST_NAME",
"COST_REVENUE_IND",
"COST_STATUS_IND",
"COST_TYPE",
"COST_TYPE_IND",
"CREATE_DT",
"CREATE_INITIALS",
CREATED,
"D_DISCH_LOC_CD",
"D_LOAD_LOC_CD",
"DELIVERY_TERM_CD",
DESCRIPTION,
"DESK_CD",
"EFFECTIVE_DT",
"ENTRY_DATE",
"EXTENDED_AMT",
"INTERNAL_CO_CD",
"LAST_UPDATED",
"LOB_CD",
"LOCK_DT",
"LUMP_SUM_COST",
"MOT_IND",
"MOVEMENT_ITEM",
"OFFICE_CD",
"PAY_DUE_DT",
"PAY_MADE_DT",
"PCR_NUM",
"PL_INCLUDE_IND",
"PORTFOLIO_CD",
"PS_IND",
"SCR_NUM",
"SOURCE_ID",
"STRATEGY_INTERNAL_CO_CD",
"STRATEGY_NAME",
"STRATEGY_NUM",
"STRATEGY_REFERENCE",
"STRATEGY_STATUS_IND",
"TRADE_LOCATION_CD",
"TRADE_NUM",
"VESSEL_CD",
"VOUCHER_AMOUNT_PAID",
"VOUCHER_APPROVED_DT",
"VOUCHER_APPROVED_IND",
"VOUCHER_APPROVER_INITIALS",
"VOUCHER_CO_CD",
"VOUCHER_CONV_FACTOR",
"VOUCHER_DT",
"VOUCHER_EXCHANGE_RT",
"VOUCHER_FINAL_PROV_IND",
"VOUCHER_FULLY_PAID_DT",
"VOUCHER_NUM",
"VOUCHER_OPERATOR_INITIALS",
"VOUCHER_PAY_CURR_CD",
"VOUCHER_STATUS_IND",
"VOUCHER_SUB_NUM",
"VOUCHER_TYPE_IND"
FROM "DATA_WAREHOUSE"."VW_ACL_COST_FACT"
WHERE trunc(CREATE_DT) >= '01 SEP 2013';

LOAD *,
CO_CD AS COUNTERPARTY;
SQL SELECT "BS_IND",
"CO_CD",
"TRADE_NUM",
"TRADE_TYPE"
FROM DATA_WAREHOUSE.VW_ACL_TRADE_FACT
WHERE TRADE_TYPE in ('Physical')
AND trunc(HDR_CREATE_DT) >= '01 NOV 2012';

LOAD [Counterparty Name],
CO_CD AS COUNTERPARTY,
[Internal Rating],
[Limit Amount],
[Unsecured Exposure],
[High Risk],
[Credit Alert],
[Credit Policy],
[Credit Policy Comment]

FROM

V:\ACL\CreditLimits.xlsx
(ooxml, embedded labels, table is Sheet1);

//-------- End Multiple Select Statements ------

[jvitantonio]

Hi Daniel, if you want the first and second table to be joined, you need to add TRADE_NUM in both LOADs. Right now, you don't have the field TRADE_NUM in the Load statment of the first table (you only have it in SQL but it's not being load to qlikview). Use this code:

LOAD *,
Date(CREATE_DT) AS CREATE_DATE,
Month(CREATE_DT) AS CREATE_DT_MONTH,
Year(CREATE_DT) AS CREATE_DT_YEAR,
MonthName(CREATE_DT) AS CREATE_DT_MONTHYEAR,
Date(PAY_DUE_DT) AS PAY_DUE_DATE,
Month(PAY_DUE_DT) AS PAY_DUE_DT_MONTH,
Year(PAY_DUE_DT) AS PAY_DUE_DT_YEAR,
MonthName(PAY_DUE_DT) AS PAY_DUE_DT_MONTHYEAR,
Date(VOUCHER_DT) AS VOUCHER_DATE,
Date(VOUCHER_FULLY_PAID_DT) AS VOUCHER_FULLY_PAID_DATE
"TRADE_NUM";
SQL SELECT "AMOUNT_DUE_IN_PAY_CURR",
"AMOUNT_DUE_IN_PRICE_CURR",
"BOOK_CD",
"CMDTY_CD",
"CO_CD",
"CO_COUNTRY_CD",
"CONTRACT_CO_CD",
"COST_CD",
"COST_CURR_CD",
"COST_NAME",
"COST_REVENUE_IND",
"COST_STATUS_IND",
"COST_TYPE",
"COST_TYPE_IND",
"CREATE_DT",
"CREATE_INITIALS",
CREATED,
"D_DISCH_LOC_CD",
"D_LOAD_LOC_CD",
"DELIVERY_TERM_CD",
DESCRIPTION,
"DESK_CD",
"EFFECTIVE_DT",
"ENTRY_DATE",
"EXTENDED_AMT",
"INTERNAL_CO_CD",
"LAST_UPDATED",
"LOB_CD",
"LOCK_DT",
"LUMP_SUM_COST",
"MOT_IND",
"MOVEMENT_ITEM",
"OFFICE_CD",
"PAY_DUE_DT",
"PAY_MADE_DT",
"PCR_NUM",
"PL_INCLUDE_IND",
"PORTFOLIO_CD",
"PS_IND",
"SCR_NUM",
"SOURCE_ID",
"STRATEGY_INTERNAL_CO_CD",
"STRATEGY_NAME",
"STRATEGY_NUM",
"STRATEGY_REFERENCE",
"STRATEGY_STATUS_IND",
"TRADE_LOCATION_CD",
"TRADE_NUM",
"VESSEL_CD",
"VOUCHER_AMOUNT_PAID",
"VOUCHER_APPROVED_DT",
"VOUCHER_APPROVED_IND",
"VOUCHER_APPROVER_INITIALS",
"VOUCHER_CO_CD",
"VOUCHER_CONV_FACTOR",
"VOUCHER_DT",
"VOUCHER_EXCHANGE_RT",
"VOUCHER_FINAL_PROV_IND",
"VOUCHER_FULLY_PAID_DT",
"VOUCHER_NUM",
"VOUCHER_OPERATOR_INITIALS",
"VOUCHER_PAY_CURR_CD",
"VOUCHER_STATUS_IND",
"VOUCHER_SUB_NUM",
"VOUCHER_TYPE_IND"
FROM "DATA_WAREHOUSE"."VW_ACL_COST_FACT"
WHERE trunc(CREATE_DT) >= '01 SEP 2013';


LOAD *,
CO_CD AS COUNTERPARTY,
"TRADE_NUM";
SQL SELECT "BS_IND",
"CO_CD",
"TRADE_NUM",
"TRADE_TYPE"
FROM DATA_WAREHOUSE.VW_ACL_TRADE_FACT
WHERE TRADE_TYPE in ('Physical')
AND trunc(HDR_CREATE_DT) >= '01 NOV 2012';


LOAD [Counterparty Name], 
CO_CD AS COUNTERPARTY, 
[Internal Rating], 
[Limit Amount], 
[Unsecured Exposure], 
[High Risk], 
[Credit Alert], 
[Credit Policy], 
[Credit Policy Comment]

FROM
V:\ACL\CreditLimits.xlsx
(ooxml, embedded labels, table is Sheet1);

[#3]

Hi Daniel,

you have to put JOIN between LOAD Statement

LOAD

*,

...

SELECT

TRADE_NUM,

...

FROM ...

;

JOIN

LOAD

*,

...

WHERE EXISTS(TRADE_NUM)

;

FROM

TRADE_NUM,

COUNTERPARTY,

...

FROM ...

;

JOIN

LOAD

*,

WHERE EXISTS(COUNTERPARTY)

;

LOAD [Counterparty Name],

CO_CD AS COUNTERPARTY,

[Internal Rating],

[Limit Amount],

[Unsecured Exposure],

[High Risk],

[Credit Alert],

[Credit Policy],

[Credit Policy Comment]

FROM

V:\ACL\CreditLimits.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards

Corrado

[pgrenier]

Hello Daniel,

QlikView shall create associations between the tables based on common field names. If there are several field names that are the same, then they will all be used to create a "Syn" Table which will act as a bridge between the two tables.

My advise would be to make sure you only have one common field between the tables you need to link together by either renaming certain columns, or by manually creating a key field during the load and keeping the original fields on only one table.

For example:

TableA:

LOAD AutoNumber(CO_CD & '~' & TRADE_NUM) as %KeyField

, CO_CD

, TRADE_NUM

, <all the other fields needed>

SQL SELECT ...

TableB:

LOAD AutoNumber(CO_CD & '~' & TRADE_NUM) as %KeyField

,  <all the other fields needed EXCEPT co_cd and trade_num>

SQL SELECT ...

Hope this helps, regards,

Philippe

[#5]

Hi,

Try this hope will solve the issue...

tbl1:

LOAD *,

//"CO_CD",

//"TRADE_NUM",

"CO_CD" &'-'& "TRADE_NUM" as Keyname,

<remaining all fields>

FROM "DATA_WAREHOUSE"."VW_ACL_COST_FACT"

WHERE trunc(CREATE_DT) >= '01 SEP 2013';

left join(tbl1)

tbl2:

LOAD *,

CO_CD AS COUNTERPARTY;

SQL SELECT "BS_IND",

"CO_CD" &'-'& "TRADE_NUM" as Keyname,

"TRADE_TYPE"

FROM DATA_WAREHOUSE.VW_ACL_TRADE_FACT

WHERE TRADE_TYPE in ('Physical')

AND trunc(HDR_CREATE_DT) >= '01 NOV 2012';

Thanx & regards,

Harshal

[danielnevitt]

Hi all,

Thank you for the above advice.  Unfortunately I am still unable to join the tables correctly.

In the tables, table 1 CO_CD is a different data column to table 2 CO_CD.  Therefore I was ideally looking for a way to just rename the second table CO_CD, whilst still importing the column.

Hope the above makes sense.

Regards,

Daniel

[#7 · Accepted solution]

Hi Daniel,

may be you need to concatenate the tables:

LOAD *,

CO_CD AS COUNTERPARTY;

SQL SELECT "BS_IND",

"CO_CD",

"TRADE_NUM",

"TRADE_TYPE"

FROM DATA_WAREHOUSE.VW_ACL_TRADE_FACT

WHERE TRADE_TYPE in ('Physical')

AND trunc(HDR_CREATE_DT) >= '01 NOV 2012';

CONCATENATE

LOAD [Counterparty Name],

CO_CD AS COUNTERPARTY,

[Internal Rating],

[Limit Amount],

[Unsecured Exposure],

[High Risk],

[Credit Alert],

[Credit Policy],

[Credit Policy Comment]

FROM

V:\ACL\CreditLimits.xlsx

(ooxml, embedded labels, table is Sheet1);

In this way you can rename a field and get the union of their values in the same field.

Be carefull to null values, infact when you concatenete two tables you get all fields in output tabel, if there isnt' a field in both table you get null values (and it's not fine, above all for the key fields), so, use default value for these cases.

Regards

Corrado.