topic Re: Joining tables in QlikView

Joining tables

anuradhaa — Mon, 22 Oct 2012 04:29:43 GMT

I have loaded data using below tables

ABC:

LOAD

XXX,

AAA_NUM,

STATUS_CODE,

AAA_DATE,

AAA_SET_ID;

SQL SELECT

XXX,

AAA_NUM,

STATUS_CODE,

AAA_DATE,

AAA_SET_ID

FROM table1 WHERE STATUS_CODE <>'5' and XXX = ($(XXX))

AND TO_CHAR(AAA_DATE, 'YYYY') IN ($(ActiveYears));

TRIAL_BALANCE_HISTORY:

LOAD

OUR_PROPORTION,

AAA_SET_ID,

TRANSACTION_DATE,

PAYMENT_METHOD;

SQL SELECT OUR_PROPORTION,

AAA_SET_ID,

TRANSACTION_DATE,

PAYMENT_METHOD FROM table2 WHERE

TO_CHAR(TRANSACTION_DATE, 'YYYY') IN ($(ActiveYears));

Then i need to get sum of our_proportion. when i use sum it gives sum for all. but i need to filler it and get only sums accoring to where clause in 1st table.

but those feils are not in the 2nd table.

how can i solve this problem

Please help

Thanks

Re: Joining tables

Mon, 22 Oct 2012 04:44:38 GMT

Hi Anuradhaa,

use left join(ABC)

Regards

Re: Joining tables

anuradhaa — Mon, 22 Oct 2012 05:02:42 GMT

Could you please help me to do that join. i'm don't know how to join tables in Qlikview

Thanks

Re: Joining tables

Mon, 22 Oct 2012 05:16:07 GMT

Hi,

ABC:

LOAD

XXX,

AAA_NUM,

STATUS_CODE,

AAA_DATE,

AAA_SET_ID;

SQL SELECT

XXX,

AAA_NUM,

STATUS_CODE,

AAA_DATE,

AAA_SET_ID

FROM table1 WHERE STATUS_CODE <>'5' and XXX = ($(XXX))

AND TO_CHAR(AAA_DATE, 'YYYY') IN ($(ActiveYears));

left join(ABC)

LOAD

OUR_PROPORTION,

AAA_SET_ID,

TRANSACTION_DATE,

PAYMENT_METHOD;

SQL SELECT OUR_PROPORTION,

AAA_SET_ID,

TRANSACTION_DATE,

PAYMENT_METHOD FROM table2 WHERE

TO_CHAR(TRANSACTION_DATE, 'YYYY') IN ($(ActiveYears));

After the join the final table wud be named ABC, u can use some meaningful name instead of this

Except this,i wud suggest that u create 2 qvds first having all the fields from both the tables w/o the where clause as it wud prevent hitting ur transaction table everytime you reload & also if u want to add some more fields from the same tables you can simply have them from the qvd.

After creating the qvd take only the fields required here with the where clause from the qvd to perform the join.

Hope it helps !!!

Regards

Re: Joining tables

anuradhaa — Mon, 22 Oct 2012 06:06:35 GMT

Thanks, join worked

But the thing i expected doesn't happen. i try to find the reson and i will explain it once i got it. appriciate your help.

Thanks

Re: Joining tables

Mon, 22 Oct 2012 06:13:37 GMT

Hi,

I had the feeling that the result might not be correct thats because even if you think you have a one-to-one relationship between the tables so that no records will be duplicated in the join, you might have have a one-to-many situation so that the number of records increases.

Also, for this case i think the TRANSACTION_DATE field in 2nd table is making a difference.

i think you need to look at the values in the fields, try joining them in Oracle query window (TO_CHAR suggests that u're using Oracle) & look at the resultant joined table. Try joining them manually on a paper by taking any particular value for the common field from both the tables and see how it looks different from the

result in QV/Oracle

Regards

Re: Joining tables

Mon, 22 Oct 2012 10:50:47 GMT

hi, Study Attached File.

Hope it will help you a lot.

Regards

-Anil

Re: Joining tables

anuradhaa — Mon, 22 Oct 2012 11:01:44 GMT

Thanks Guys,

Thanks a lot .

Re: Joining tables

Mon, 22 Oct 2012 11:03:21 GMT

Hi,

if u're able to get the deisred result, please mark the question as answered or assumed answered.

Regards