Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

divya_anand
Contributor II

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.

Tags (1)
1 Solution

Accepted Solutions

Re: Compare 2 fields & display both records.

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

10 Replies

Re: Compare 2 fields & display both records.

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
Contributor II

Re: Compare 2 fields & display both records.

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.

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
Contributor II

Re: Compare 2 fields & display both records.

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.

stalwar1

Do you have any idea

Re: Compare 2 fields & display both records.

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

Re: Compare 2 fields & display both records.

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
Contributor II

Re: Compare 2 fields & display both records.

Hi Sunny,

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

divya_anand
Contributor II

Re: Compare 2 fields & display both records.

Hi Tamil,

Thank you very much. Really appreciate it.

Community Browser