Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
divya_anand
Creator III
Creator III

Compare 2 fields & display both records.

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

10 Replies
tamilarasu
Champion
Champion

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;

divya_anand
Creator III
Creator III
Author

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.

tamilarasu
Champion
Champion

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),',' & TempKeyas Key1
Resident Data;

DROP Table Data;

Output:

Capture.PNG

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

divya_anand
Creator III
Creator III
Author

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.

tamilarasu
Champion
Champion

stalwar1

Do you have any idea

sunny_talwar

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;

Capture.PNG

tamilarasu
Champion
Champion

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.

divya_anand
Creator III
Creator III
Author

Hi Sunny,

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

divya_anand
Creator III
Creator III
Author

Hi Tamil,

Thank you very much. Really appreciate it.