Merging two tables

[Jak007]

Hello Community,

I am quite new to QlikSense and looking for a solution. Here is a case and things I want to achieve :

I have 2 tables in the database -

Table 1 : Company

ID	Company name	Legacy_ID
1	A
2	B
3	C	7
4	D	9
5	E	8

Table 2 : Revenue

ID	Revenue
1	50
2	600
8	200
9	400
6	100

I want to merge those 2 tables and want to get the following data in 3 tables :
Table 1 : Match

ID	Company name	Revenue
1	A	50
2	B	600
8	E	200
9	D	400

Table 2 :  No match of Company table

ID	Company name	Legacy_ID
3	C	7

Table 3 : No match of Revenue table

ID	Revenue
6	100

I have tried to use join and concatenate, also tried mapping with flags. I am not able to get the result that I am looking for. If somebody can help me that would be great.

Thank you.

[PrashantSangle]

Hello,

For 1: 

Load * from Company;

Left Join

Load * from Revenue;

For 2

Revenue:

Load * Revenue;

Noconcatenate

Load * Company

where not exists(ID,ID);

For 3

Company:

Load * Company;

NoConcatenate

Load * Revenue

where not exists(ID,ID);

Regards,

Prashant Sangle

[Jak007]

Hello Prashant,

Thank you for your reply. As per your solution For 1, QlikSense is joining tables on ID only, I want to join on ID but if that doesn't match I want to join using Legacy ID.

For 1: 

Load * from Company;

Left Join

Load * from Revenue;

Thank you

[sidhiq91]

@Jak007  Please see the code below:

For point 1:

NoConcatenate
Temp:
load ID,
Companyname,
if(isnull(emptyisnull(Legacy_ID)),ID,Legacy_ID) as ID1

inline [
ID,Companyname,Legacy_ID
1, A,
2, B,
3, C, 7
4, D, 9
5, E, 8
];

NoConcatenate
Temp1:
Load ID1,
Companyname,
ID
Resident Temp;

Inner join (Temp1)
Temp2:
load ID as ID1,
Revenue

inline [
ID, Revenue
1, 50
2, 600
8, 200
9, 400
6, 100
];

Drop table Temp;

Exit Script;

For point 2:

NoConcatenate
Temp:
load ID,
Companyname,
if(isnull(emptyisnull(Legacy_ID)),ID,Legacy_ID) as ID1

inline [
ID,Companyname,Legacy_ID
1, A,
2, B,
3, C, 7
4, D, 9
5, E, 8
];

left join (Temp)
Temp2:
load ID as ID1,
Revenue

inline [
ID, Revenue
1, 50
2, 600
8, 200
9, 400
6, 100
];

NoConcatenate
Temp3:
Load *
Resident Temp
where isnull(Revenue);

Drop table Temp;

exit script;

For point 3: 

NoConcatenate
Temp:
load ID,
Companyname,
if(isnull(emptyisnull(Legacy_ID)),ID,Legacy_ID) as ID1

inline [
ID,Companyname,Legacy_ID
1, A,
2, B,
3, C, 7
4, D, 9
5, E, 8
];

NoConcatenate
Temp1:
Load *
Resident Temp;
Drop table Temp;

NoConcatenate
Temp2:
load ID as ID1,
Revenue

inline [
ID, Revenue
1, 50
2, 600
8, 200
9, 400
6, 100
]

where not Exists(ID1,ID);

Drop table Temp1;
Exit Script;

If this resolves your issue, please like and accept it as a solution.