Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

poooja
New Contributor III

Connecting two tables

Dear Qlikies,

I have an issue while connecting 2 tables. i kept a common column and joined the table but it's not giving correct output.

Please guide me how to connect this 2 table to get right output.

Requirement is: i need to get the data of amount spend by USD from Table B. Output table should have below columns.

Table A  

tablea.JPG

 

 

 

 

 

 

 

 

 

Table B 

tableb.JPG

 

 

 

 

 

 

 

Expected Out Put

tableoutput.JPG

 

Thanks! 

Pooja

 

10 Replies
Highlighted
shiveshsingh
Honored Contributor

Re: Connecting two tables

How come your Table2 and Expected table is same?

Can you check it once?

poooja
New Contributor III

Re: Connecting two tables

Hi,

 

I have corrected the expected table.

Thanks!

Pooja

Keitaru
New Contributor III

Re: Connecting two tables

Join left ?
poooja
New Contributor III

Re: Connecting two tables

Hi,

Left Join is not giving right total amount. let me know any other suggestion.

Thanks.

Pooja

Re: Connecting two tables

do the left join by using key of ID and curr_used. try below

Load id,
type,
total_amount,
curr_used,
id&'-'&curr_used as key
from table1;

Left Join
Load
distinct
sub_id &'-'&curr as key,
Name
From table2;


Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
poooja
New Contributor III

Re: Connecting two tables

This is not working. i am not getting right total_amt.

Suggesting any other way?

 

Thanks,

Pooja.

 

Re: Connecting two tables

what do you mean by not working?? can share screen shot it.

Regards,
PRashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
imrasyed
Contributor

Re: Connecting two tables

Hi

Please see the output screenshot for the below script.

I am getting the expected output upon selection of CURR_USD='usd'

 

 

TableA:
Load * Inline
[
ID,TYPE,Total_Amount,CURR_USD
10A,car,100,cad
10A,home,200, cad
10A,home,200, usd
10B,hotel,200, eur
10B,Restraurant,300, eur
10C,Flight,500, usd
10D,Restraurant,500, usd
20A,Restraurant,300, usd
20A,hotel,400, usd
20B,home,200, gbp
];

TableB:
Load * Inline
[
ID, ExpAmount, Curr, Name
10A, 500, usd, john
10B, 500, eur, john
10C, 500, usd, john
20A, 700, usd, peter
20B, 200, gbp, peter
10D, 500, usd, john
];

 

Capture.PNG

abednego
New Contributor II

Re: Connecting two tables

Hi
Select data from TableA and TableB. Populate the data. Drag TableB on TableA and select ID-Sub_ID as your join. Then create your Output Table with an '=if(CURR_USED='USD',CURR_USED)' Expression for Curr_Used.