Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser22
Creator II
Creator II

Join with multiple common fields in qliksense

Hi

I am very new to qliksense and scripting in qlik seems to be very hard and intersting at the beginning. I need help in joining multiple tables with multiple common fields.

Please help me. 

 

exist_in_flag:
mapping Load
sbscrnum,
1 as exist_in_table
FROM [lib://AttachedFiles/table2.xlsx]
(ooxml, embedded labels, table is Sheet1);


T1:

LOAD
sbscrnum,
companyid,
invno,
addr,
city,
country,
Moneyy,
ApplyMap('exist_in_flag',sbscrnum,0) as exist_in_flag
FROM [lib://AttachedFiles/table1.xlsx]
(ooxml, embedded labels, table is Sheet1);

T2:
NoConcatenate
LOAD
sbscrnum,
companyid,
invoiceno as invno,
Moneyy
FROM [lib://AttachedFiles/table2.xlsx]
(ooxml, embedded labels, table is Sheet1);

LEFT JOIN(T2)
LOAD sbscrnum,Moneyy,
addr,city,country
Resident T1
where exist_in_flag = 1 ;

Concatenate
LOAD sbscrnum,invno,companyid,addr,city,country,Moneyy
Resident T1
where exist_in_flag = 0;

T3:
NoConcatenate
LOAD *
Resident T2;

JOIN(T2)

LOAD
sbscrnum,
speedname,
Type,
subscriptionaddr as addr,
subcity as city,
subcountry as country
FROM [lib://AttachedFiles/table3.xlsx]
(ooxml, embedded labels, table is Sheet1);

I am getting output like this

qlikuser22_0-1641908185118.png

 

But I need something like this, 

qlikuser22_2-1641908213894.png

Data sample:

qlikuser22_0-1641909199303.png

 

I tried joins and concatenate. The main idea is I need addr,city and country fields to be matched. As I am very new to this. Please guide me how to proceed. Thanks a lot in advance

Labels (4)
0 Replies