topic Re: Connecting two tables in App Development

Connecting two tables

poooja — Thu, 09 May 2019 08:31:16 GMT

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

Table B

Expected Out Put

Thanks!

Pooja

Re: Connecting two tables

shiveshsingh — Thu, 09 May 2019 05:28:33 GMT

How come your Table2 and Expected table is same?

Can you check it once?

Re: Connecting two tables

poooja — Thu, 09 May 2019 08:32:18 GMT

Hi,

I have corrected the expected table.

Thanks!

Pooja

Re: Connecting two tables

Keitaru — Fri, 10 May 2019 06:56:37 GMT

Join left ?

Re: Connecting two tables

poooja — Tue, 14 May 2019 02:31:33 GMT

Hi,

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

Thanks.

Pooja

Re: Connecting two tables

PrashantSangle — Tue, 14 May 2019 04:06:51 GMT

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

Re: Connecting two tables

poooja — Tue, 14 May 2019 06:48:49 GMT

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

Suggesting any other way?

Thanks,

Pooja.

Re: Connecting two tables

PrashantSangle — Tue, 14 May 2019 07:02:39 GMT

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

Regards,
PRashant Sangle

Re: Connecting two tables

imrasyed — Tue, 14 May 2019 07:49:47 GMT

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
];

Re: Connecting two tables

abednego — Tue, 14 May 2019 08:30:45 GMT

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.

Re: Connecting two tables

PrashantSangle — Tue, 14 May 2019 09:47:24 GMT

Hi,
try below

TableA:
Load ID,TYPE,Total_Amount,CURR_USD,
ID&'-'&CURR_USD as key
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
];

Left Join

Load ID&'-'&Curr as key,
Name
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
];

Regards,
PRashant Sangle