How to compare multiple columns from 2 different sources

[kalavala_vijayk]

Hi Qlik experts,

I would like to compare diff. columns from diff. sources. same like how we do in excel with pivots.

Source- 1

Employee ID	Name	Organisation	Role
1001	ABC	Sales	Manager
1002	BCD	Marketing	Associate
1003	CDE	Operations	Sr. Manager
1004	DEF	Operations	Associate

Source-2

Employee ID	Name	Organisation	Role
1001	ABC	Sales	Manager
1002	BCD	Marketing	Associate
1003	CDE	Operations	Sr. Manager
1005	DEF	Marketing	Director

Output in Qlik

Description	Source-1	Source-2	Difference
Sales	1	1	0
Marketing	1	2	1
Operations	2	1	-1

So that i can compare if migration is correct.

Thanks for your suggestions.

[Kushal_Chawda]

@kalavala_vijayk  why Marketing is repeated in your Output?

[chinmayadash07]

Yes You can do the same, Please do the follow steps.

Data:

load * ,Source 1 as Flag  from Source 1;

concatenate

load * ,Source 2 as Flag  from Source 2;

then create a pivot table in qliksense and take organisation as dimesnion and Flag as column and create measure (count(organisation))

and then create an another measure called difference where u can write =count(organisation) - before(count(organisation),1) measure to achieve the same

Else you can write the same calculation in script also

[kalavala_vijayk]

@Kushal_Chawda  - Thanks , i have corrected.

[Kushal_Chawda · Accepted solution]

@kalavala_vijayk  if field names are same in both sources then you could try below, otherwise you need to rename it to make sure that names are matching in both sources

Data:
LOAD *,
     'Source 1' as Source
FROM Source1;

concatenate(Data)
LOAD *,
     'Source 2' as Source
FROM Source2;

Now, create a table with below Dimension & Measure

Dimension:

Organization

Measure:

1) =Count({<Source={'Source 1'}>}Organisation)    // Source 1

2) =Count({<Source={'Source 2'}>}Organisation)    // Source 2

3) =Count({<Source={'Source 2'}>}Organisation)-Count({<Source={'Source 1'}>}Organisation) // Difference