Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anuradhaa
Partner - Creator II
Partner - Creator II

Joining tables

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

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

8 Replies
Not applicable

Hi Anuradhaa,

use left join(ABC)

Regards

anuradhaa
Partner - Creator II
Partner - Creator II
Author

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

Thanks

Not applicable

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

anuradhaa
Partner - Creator II
Partner - Creator II
Author

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

Not applicable

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

Not applicable

hi, Study Attached File.

Hope it will help you a lot.

Regards

-Anil

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Thanks Guys,

Thanks a lot .

Not applicable

Hi,

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

Regards