topic Re: SQL Table Join in QlikView

SQL Table Join

danielnevitt — Wed, 12 Mar 2014 16:04:44 GMT

Hi,

I would really appreciate some help with the following SQL load statement.

I am trying to join the first table with the second table when OFFICE_CD,DESK_CD, CONTRACT_DT,PRICE1 and CO_CD equal an entry in the opposite table. The reason for spliting the data into two tables is to ensure that a buy is match with a sell.

Is there an easier way to code this? If not, is there something obvious that is incorrect with my code.

Thanks for any help.

Regards,

Daniel

vToday1 = Date(Today(),'DD MMM YYYY');
ODBC CONNECT32 TO [Tempest_DCF_Prod;DBQ=DCFDWP1] (XUserId is DCCXQANIMaXKXZVMSDRB, XPassword is YVbVPSNGWLMGWZMOaLYA);
//-------- Start Multiple Select Statements ------
LOAD *,
DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,
BS_IND AS A_BS_IND,
CMDTY_CD AS A_CMDTY_CD,
CO_CD AS A_CO_CD,
CONTRACT_DT AS A_CONTRACT_DT,
CONTRACT_QTY AS A_CONTRACT_QTY,
DESK_CD AS A_DESK_CD,
EXPIRE_DT AS A_EXPIRE_DT,
OFFICE_CD AS A_OFFICE_CD,
PERIOD_CD AS A_PERIOD_CD,
PRICE1 AS A_PRICE1,
TRADE_NUM AS A_TRADE_NUM,
TRADE_STATUS_IND AS A_TRADE_STATUS_IND,
TRADE_TYPE AS A_TRADE_TYPE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND   trunc(EXPIRE_DT) > '$(vToday1)'
AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND   BS_IND IN ('Buy');

INNER JOIN LOAD *,
DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,
BS_IND AS B_BS_IND,
CMDTY_CD AS B_CMDTY_CD,
CO_CD AS B_CO_CD,
CONTRACT_DT AS B_CONTRACT_DT,
CONTRACT_QTY AS B_CONTRACT_QTY,
DESK_CD AS B_DESK_CD,
EXPIRE_DT AS B_EXPIRE_DT,
OFFICE_CD AS B_OFFICE_CD,
PERIOD_CD AS B_PERIOD_CD,
PRICE1 AS B_PRICE1,
TRADE_NUM AS B_TRADE_NUM,
TRADE_STATUS_IND AS B_TRADE_STATUS_IND,
TRADE_TYPE AS B_TRADE_TYPE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND   trunc(EXPIRE_DT) > '$(vToday1)'
AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND   BS_IND IN ('Sell')
AND   A_DESK_CD = B_DESK_CD
AND   A_OFFICE_CD = B_OFFICE_CD
AND   A_CO_CD = B_CO_CD
AND   A_CONTRACT_DT = B_CONTRACT_DT
AND   A_PRICE1 = B_PRICE1;

Re: SQL Table Join

jpapador — Wed, 12 Mar 2014 16:10:34 GMT

Is there a specific problem you are having with that script?

Re: SQL Table Join

Wed, 12 Mar 2014 16:11:08 GMT

Hey Daniel,

I think where exists() will work for you in this case(you can use it in the preload ).

Example:

Table1:

Load a,b,c,*;

SQL Select *;

Inner Join

Load a,b,c,*

Where exists(a) and exists(b) and exists(c);

SQL Select *;

Thanks

Re: SQL Table Join

danielnevitt — Wed, 12 Mar 2014 16:15:40 GMT

Hi jpapador,

The script load fails with the message stating:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "B_PRICE1": invalid identifier

SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND   trunc(EXPIRE_DT) > '12 Mar 2014'
AND   TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND   BS_IND IN ('Sell')
AND   A_DESK_CD = B_DESK_CD
AND   A_OFFICE_CD = B_OFFICE_CD
AND   A_CO_CD = B_CO_CD
AND   A_CONTRACT_DT = B_CONTRACT_DT
AND   A_PRICE1 = B_PRICE1

Regards,

Daniel

Re: SQL Table Join

danielnevitt — Wed, 12 Mar 2014 16:18:21 GMT

Hi Ajay,

Thanks for your reply.

With regards to the where exists() function. Will this return only values where the OFFICE_CD, DESK_CD,CO_CD, CONTRACT_DT etc are the same in the second table as the are in the first table (on a line by line basis)?

Regards,

Daniel

Re: Re: SQL Table Join

Wed, 12 Mar 2014 16:25:05 GMT

Yes. Its on a line by line basis. Only if all those exists on the same line in Table1 then that record will be brought in from Table2.

Thanks

Re: Re: SQL Table Join

danielnevitt — Wed, 12 Mar 2014 16:29:09 GMT

Hi Ajay,

Thanks, that's really helpful.

Last question. If I specify that Table2 should only show 'Sell' trades (AND BS_IND IN ('Sell')) after the where exists statement that you suggest. Will this still work?

Thanks

Daniel

Re: SQL Table Join

Wed, 12 Mar 2014 16:34:02 GMT

Sure something like this should work:

Inner Join

Load a,b,c,*

Where exists(a) and exists(b) and exists(c) and BS_IND='Sell' ;

SQL Select *;

Re: Re: SQL Table Join

Wed, 12 Mar 2014 16:45:44 GMT

A small correction to what I said. Instead of doing where exists individually please create a key out of it and try it.

Table1:

Load autonumberhash128(a,b,c) as Key,*;

SQL Select *;

Inner Join

Load *

Where exists(Key,Key1);

Load Autonumberhash128(a,b,c) as Key1,*;

SQL Select *;

Re: Re: SQL Table Join

danielnevitt — Wed, 12 Mar 2014 17:07:14 GMT

Hi Ajay,

Sorry I'm now slightly confused how to code this.

How should I write the statement to only join on the 5 fields stated above, but also still show the other fields. I also want to restrict the data to (as per the SQL load):

WHERE TRADE_TYPE in ('Future')

AND trunc(EXPIRE_DT) > '$(vToday1)'

AND TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')

Thanks,

Daniel

Re: SQL Table Join

Wed, 12 Mar 2014 17:12:48 GMT

Ahh.... now I see what you want. In this case you dont have to use exists. Just a Left Join. Like below:

vToday1 = Date(Today(),'DD MMM YYYY');

ODBC CONNECT32 TO [Tempest_DCF_Prod;DBQ=DCFDWP1] (XUserId is DCCXQANIMaXKXZVMSDRB, XPassword is YVbVPSNGWLMGWZMOaLYA);

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

LOAD *,

DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,

BS_IND AS A_BS_IND,

CMDTY_CD AS A_CMDTY_CD,

CO_CD AS A_CO_CD,

CONTRACT_DT AS A_CONTRACT_DT,

CONTRACT_QTY AS A_CONTRACT_QTY,

DESK_CD AS A_DESK_CD,

EXPIRE_DT AS A_EXPIRE_DT,

OFFICE_CD AS A_OFFICE_CD,

PERIOD_CD AS A_PERIOD_CD,

PRICE1 AS A_PRICE1,

TRADE_NUM AS A_TRADE_NUM,

TRADE_STATUS_IND AS A_TRADE_STATUS_IND,

TRADE_TYPE AS A_TRADE_TYPE;

SQL SELECT *

FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"

WHERE TRADE_TYPE in ('Future')

AND trunc(EXPIRE_DT) > '$(vToday1)'

AND TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')

AND BS_IND IN ('Buy');

Left JOIN

LOAD *,

DESK_CD&OFFICE_CD&CONTRACT_DT&PRICE1&CO_CD AS JOIN_REFERENCE,

BS_IND AS B_BS_IND,

CMDTY_CD AS B_CMDTY_CD,

CO_CD AS B_CO_CD,

CONTRACT_DT AS B_CONTRACT_DT,

CONTRACT_QTY AS B_CONTRACT_QTY,

DESK_CD AS B_DESK_CD,

EXPIRE_DT AS B_EXPIRE_DT,

OFFICE_CD AS B_OFFICE_CD,

PERIOD_CD AS B_PERIOD_CD,

PRICE1 AS B_PRICE1,

TRADE_NUM AS B_TRADE_NUM,

TRADE_STATUS_IND AS B_TRADE_STATUS_IND,

TRADE_TYPE AS B_TRADE_TYPE;

SQL SELECT *

FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"

WHERE TRADE_TYPE in ('Future')

AND trunc(EXPIRE_DT) > '$(vToday1)'

AND TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')

AND BS_IND IN ('Sell');

Re: SQL Table Join

danielnevitt — Wed, 12 Mar 2014 17:24:49 GMT

Thanks Ajay.

Is there a way to only show the records in both tables where the JOIN_REFERENCE matches?

My understanding is that the above will only join table 2 data to table 1 where the JOIN_REFERENCE is the same.

Regards,

Daniel

Re: SQL Table Join

maxgro — Wed, 12 Mar 2014 17:31:44 GMT

inner join instead of left join

from help online

If used before join it specifies that an inner join should be used. The resulting table will thus only contain combinations of field values from the raw data tables where the linking field values are represented in both tables.

Re: SQL Table Join

Wed, 12 Mar 2014 17:32:56 GMT

Ok in that case the same code as above. Just change left join to inner join.

You don't need a separate IN Sql command in the second table for those 5 fields.

Since they are part of the key, when you do an inner join they will automatically join with each other based on common JOIN_REFERENCE field values.

Re: SQL Table Join

danielnevitt — Wed, 12 Mar 2014 17:34:53 GMT

Thanks for all your help.

Regards,

Daniel

Re: Re: SQL Table Join

danielnevitt — Thu, 13 Mar 2014 09:53:46 GMT

Hi,

I have tried to run the script (see below) this morning and it is not producing any results.

It would be great if someone could help by answering the following questions:

1) Is there a way of restricting the load from both tables to only the new fields I have created?

2) Is the statement I have written the best way to achieve trying to show where a buy and a sell are matched on specific fields?

Any help will be much appreciated.

Regards,

Daniel

LET vToday1 = Date(Today(),'DD MMM YYYY');

LOAD *,
BS_IND AS A_BS_IND,
CMDTY_CD AS A_CMDTY_CD,
CO_CD AS A_CO_CD,
CONTRACT_DT AS A_CONTRACT_DT,
CONTRACT_QTY AS A_CONTRACT_QTY,
DESK_CD AS A_DESK_CD,
EXPIRE_DT AS A_EXPIRE_DT,
OFFICE_CD AS A_OFFICE_CD,
PERIOD_CD AS A_PERIOD_CD,
PRICE1 AS A_PRICE1,
TRADE_NUM AS A_TRADE_NUM,
TRADE_STATUS_IND AS A_TRADE_STATUS_IND,
TRADE_TYPE AS A_TRADE_TYPE,
DESK_CD&OFFICE_CD&CONTRACT_DT&CO_CD AS JOIN_REFERENCE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND trunc(EXPIRE_DT) > '$(vToday1)'
AND TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND BS_IND IN ('Buy');

INNER JOIN
LOAD *,
BS_IND AS B_BS_IND,
CMDTY_CD AS B_CMDTY_CD,
CO_CD AS B_CO_CD,
CONTRACT_DT AS B_CONTRACT_DT,
CONTRACT_QTY AS B_CONTRACT_QTY,
DESK_CD AS B_DESK_CD,
EXPIRE_DT AS B_EXPIRE_DT,
OFFICE_CD AS B_OFFICE_CD,
PERIOD_CD AS B_PERIOD_CD,
PRICE1 AS B_PRICE1,
TRADE_NUM AS B_TRADE_NUM,
TRADE_STATUS_IND AS B_TRADE_STATUS_IND,
TRADE_TYPE AS B_TRADE_TYPE,
DESK_CD&OFFICE_CD&CONTRACT_DT&CO_CD AS JOIN_REFERENCE;
SQL SELECT *
FROM "DATA_WAREHOUSE"."VW_ACL_TRADE_FACT"
WHERE TRADE_TYPE in ('Future')
AND trunc(EXPIRE_DT) > '$(vToday1)'
AND TRADE_STATUS_IND NOT IN ('Cancelled','Deleted')
AND BS_IND IN ('Sell');