Compare two variables from two tables and get the difference

Mon, 20 Feb 2017 08:31:45 GMT

Dear community.

I have a problem I cannot solve myself, and I have tried to look around in the community on solutions for similar problems, but they do not seek to work in my case.

I have two variables, which each contains PersonID's. The variables exist in two different tables and are not linked by any key.

Both tables are loaded to my QlikMart and my application.

Tabel 1		Tabel 2
ID_A	Count	ID_B	Count
25	1	24	1
26	1	26	1
27	1	27	1
28	1	29	1
29	1	30	1
30	1	31	1
32	1

Now I want to compare the PersonID's in the two variables (ID_A and ID_B) and find out which PersonID's that exist in both tables, and which only exist in one of them nnd in the last case, in which tables it then exists.

In Excel I would make the two tables into one and make a new variable saying in which table the PersonID's are from.

Tabel 3
ID_C	Count	Tabel
25	1	Tabel 1
26	1	Tabel 1
27	1	Tabel 1
28	1	Tabel 1
29	1	Tabel 1
30	1	Tabel 1
24	1	Tabel 2
26	1	Tabel 2
27	1	Tabel 2
29	1	Tabel 2
30	1	Tabel 2
31	1	Tabel 2
32	1	Tabel 2

Then I would make the table into a pivottable, so I get the following result where I can identify the PersonID's that only exist in one of the tables.

ID_C	Tabel 1	Tabel 2	Total
28	1		1
24		1	1
25	1		1
32		1	1
31		1	1
29	1	1	2
27	1	1	2
30	1	1	2
26	1	1	2
Total	6	7	13

I hope that some one has an idea of how I can get the result I want.

And I hope that it doesn't involve coding in the script, but that it can be solved by a special expression e.g. by using the agr() - function.

Please ask if anything is unclear.

In advance, thanks!

Kind regards

Line

Re: Compare two variables from two tables and get the difference

settu_periasamy — Mon, 20 Feb 2017 14:43:30 GMT

May be check the attachment

Re: Compare two variables from two tables and get the difference

Mon, 20 Feb 2017 15:00:14 GMT

Thank you so much for replying.

However, I cannot as you do, load the tables, so the PersonID's have the same name.

Therefore, if possible, I would like to make the solution directly in my application and not in the script.

Kind regards

Line

topic Re: Compare two variables from two tables and get the difference in QlikView

Compare two variables from two tables and get the difference

Re: Compare two variables from two tables and get the difference

Re: Compare two variables from two tables and get the difference