topic Re: Common/Uncommon data in Two or more tables in QlikView

Common/Uncommon data in Two or more tables

Thu, 24 Oct 2013 05:51:04 GMT

Hi All,

We have an issue here.

We are trying to find out the common and uncommon data from two fields from two separate tables and then we are trying to add another filed (Flag) to the table which has the maximum values, which will have a value YES if data matches and NO if the data is not matching.

Please find the data as pasted below.

Table1	Table2
Col1	Col2
A	A
B	B
C	X
D	E
E	D
F	U
	R
	S
	T

Result
Col2	New column
A	Yes
B	Yes
X	No
E	Yes
D	Yes
U	No
R	No
S	No
T	No

Thanks in advance

Re: Common/Uncommon data in Two or more tables

tresB — Thu, 24 Oct 2013 05:54:37 GMT

Try like:

Load

Col1,

Col2,

If(Col1=Col2, 'Yes', 'No') as NewCol

Re: Common/Uncommon data in Two or more tables

Thu, 24 Oct 2013 06:07:21 GMT

Thanks for your help but Col1 and Col2 are fields of two different tables and those keys are primary foreign keys. I have linked those tables with the help of these columns... Please give me solutions for this

Re: Common/Uncommon data in Two or more tables

tresB — Thu, 24 Oct 2013 06:13:11 GMT

Could you share a sample app?

Re: Common/Uncommon data in Two or more tables

sushil353 — Thu, 24 Oct 2013 06:13:51 GMT

Try the below code:

Table1:

LOAD * Inline

[

Col1

];

Table2:

LOAD Col1 as Col2,if(Exists(Col1),'Yes','No') as flag Inline

[

Col1

];

Its output is:

A	Yes
B	Yes
D	Yes
E	Yes
R	No
S	No
T	No
U	Yes
X	Yes

HTH

Sushil

Re: Common/Uncommon data in Two or more tables

Jason_Michaelides — Thu, 24 Oct 2013 06:19:57 GMT

Hi,

You can use ApplyMap() for this:

Map_TableA:

MAPPING LOAD

Col1,

'Yes'

From...Table1;

Table2:

LOAD

Col2,

ApplyMap('Map_Table1',Col2,'No') AS [New Column]

From....Table2;

Hope this helps,

Jason

Re: Common/Uncommon data in Two or more tables

Fri, 25 Oct 2013 09:12:40 GMT

Thank you guys !!!