topic Re: Compare 2 fields & display both records. in QlikView

Compare 2 fields & display both records.

divya_anand — Mon, 19 Dec 2016 05:54:32 GMT

Hi all,

I am seeking a solution where I can compare the fields code1 & code 2 which belong to same table and if it matches then the output should be both the keys.

For eg. in the below screenshot, since 111 matches in code1 & code2, It should display "A01" and "B02".

Can someone please help me here.

Thank you very much.

Re: Compare 2 fields & display both records.

tamilarasu — Mon, 19 Dec 2016 06:05:24 GMT

Hi Divya,

Try like this

Data:
LOAD Code1,

Code2,

Key1
From Source;

Left Join
Load Code2 as Code1,
Key1 as TempKey
Resident Data;

Result:
Load Code1,
Code2,
Key1 & If(Not isnull(TempKey),',' & TempKey) as Key1
Resident Data;

DROP Table Data;

Re: Compare 2 fields & display both records.

divya_anand — Mon, 19 Dec 2016 08:19:54 GMT

Hi Tamil,

what if there are more fields instead of just one (key1)?

So, if code1 = code2 then both the records with respect to code1 & code2 are to be displayed in table.

Thank you for the response.

Re: Compare 2 fields & display both records.

tamilarasu — Mon, 19 Dec 2016 08:44:56 GMT

Hello Divya,

Maybe this?

Data:

LOAD * INLINE [
Code1, Code2, Key1, Key2, Key3
111, 272, 001, 02, 02
112, 282, A02, 04, 02
113, 293, A03, 05, 03
114, 263, A04, 06, 04
122, 111, B02, 07, 05
];

Left Join
Load Code2 as Code1,
Key1 & ', ' & Key2 & ', ' & Key3 as TempKey
Resident Data;

Result:
Load Code1,
Code2,
Key1 & If(Not IsNull(TempKey),',' & TempKey) as Key1
Resident Data;

DROP Table Data;

Output:

If this is not your expectation, please post a sample and expected result. Thanks

Re: Compare 2 fields & display both records.

divya_anand — Mon, 19 Dec 2016 13:46:01 GMT

Hi Tamil,

Thank you for the response again.

What I'm looking for is- Say I have data as given below.

So, when I create a straight table and if I search for 111 in code1, I see only that record in the straight table:

I need help in finding a way to see both records where code1=111 and code2=111 like this:

So, the records where there is a match in code1 & code2, should display both the records.

In case you need more information, kindly let me know.

Thank you.

Re: Compare 2 fields & display both records.

tamilarasu — Tue, 20 Dec 2016 03:01:00 GMT

stalwar1

Do you have any idea

Re: Compare 2 fields & display both records.

sunny_talwar — Tue, 20 Dec 2016 03:10:23 GMT

May be using LinkTable?

Table:

LOAD RowNo() as Key,

LOAD * INLINE [

Code1, Code2, Key1, location, description

111, 272, A01, X, madsmadbh

112, 282, A02, X, adshagdhsa

113, 293, A03, X, adkjsadh

113, 299, A03, X, asdsaf

114, 263, A04, X, sfdvgszf

115, 219, A05, X, sfdfgstg

116, 200, A06, X, sdfgdsrsd

121, 271, B01, Y, sfdbgfd

122, 111, B02, Y, sgdgsyhdbrv

123, 290, B03, Y, sfsghtum

123, 296, B03, Y, jhjfghdfby

124, 264, B04, Y, turhd

125, 113, B05, Y, btjhdg

126, 204, B06, Y, uyergfd

];

LinkTable:

LOAD DISTINCT Code1 as Code,

Key

Resident Table;

Concatenate (LinkTable)

LOAD DISTINCT Code2 as Code,

Key

Resident Table;

Re: Compare 2 fields & display both records.

tamilarasu — Tue, 20 Dec 2016 04:10:37 GMT

Good one. I too had a thought of creating a link lin table. I suppose Divya wants to make a selections in Code1 and Code2 field and vice versa. Let's wait.

Re: Compare 2 fields & display both records.

divya_anand — Tue, 20 Dec 2016 04:41:43 GMT

Hi Sunny,

This is perfect. Thank you very much. helped me a lot.

Re: Compare 2 fields & display both records.

divya_anand — Tue, 20 Dec 2016 04:42:14 GMT

Hi Tamil,

Thank you very much. Really appreciate it.

Re: Compare 2 fields & display both records.

tamilarasu — Tue, 20 Dec 2016 04:50:22 GMT

Hi Divya,

No problem. If you got the solution, please close the thread by marking Sunny's reply as correct answer. Have a good day.