Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Anuradhaa,
use left join(ABC)
Regards
Could you please help me to do that join. i'm don't know how to join tables in Qlikview
Thanks
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
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
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
hi, Study Attached File.
Hope it will help you a lot.
Regards
-Anil
Thanks Guys,
Thanks a lot .
Hi,
if u're able to get the deisred result, please mark the question as answered or assumed answered.
Regards