topic Re: Match multiple value in 2 tables in QlikView

Match multiple value in 2 tables

jimbo_carlos — Wed, 25 Nov 2020 16:16:04 GMT

Hello!

Thank you for looking into this question.

I want to use the Repair ID in Table2 to compare with Table1 to count for how many entry is match there. In this case, the result would be 2. May I know any functions can achieve this? Thanks

Data Table1 from OBDC:

RepairID CarPlateID

1001 MAZ1

1002 MAZ2

1003 MAZ3

1004 MAZ4

1005 MAZ5

1006 MAZ6

1007 MAZ7

Data Table2 from OBDC:

RepairID

1006

1007

1008

1009

1010

1011

Re: Match multiple value in 2 tables

isingh30 — Mon, 11 Jun 2018 15:38:54 GMT

Use Lookup function

Re: Match multiple value in 2 tables

Anonymous — Mon, 11 Jun 2018 15:46:09 GMT

Hello,

Try something like this:

Table1:

Load * Inline

[RepairID, CarPlateID

1001, MAZ1

1002, MAZ2

1003, MAZ3

1004, MAZ4

1005, MAZ5

1006, MAZ6

1007, MAZ7

];

Table2:

Load * Inline

[

RepairID

1006

1007

1008

1009

1010

1011

];

Common:

Load RepairID As CommonIDs

Resident Table2;

Inner Join

Load RepairID As CommonIDs

Resident Table1;

After that, you can use this to get the number of common ID's between tables:

=Count(CommonIDs)

Hope this helps!

Re: Match multiple value in 2 tables

vishsaggi — Mon, 11 Jun 2018 15:46:50 GMT

May be this?

Table1:

LOAD * INLINE [

RepairID, CarPlateID

1001, MAZ1

1002, MAZ2

1003, MAZ3

1004, MAZ4

1005, MAZ5

1006, MAZ6

1007, MAZ7

1008, MAZ8

];

LEFT JOIN(Table1)

Table2:

LOAD *, RowNo() AS RowCnt INLINE [

RepairID

1006

1007

1008

1009

1010

1011

];

Use a count(RowCnt) in your textobject or any chart as an expression.

Or just use

1 AS RowCnt in table2 replacing RowNo().

Re: Match multiple value in 2 tables

jimbo_carlos — Tue, 12 Jun 2018 01:10:06 GMT

Thank you all for looking into this! May I know is it possible to perform calculation in Expression level rather than in the script?

Re: Match multiple value in 2 tables

qlikviewwizard — Tue, 12 Jun 2018 01:51:26 GMT

Hi,

Check this.

Table1:

Load RepairID,CarPlateID,RepairID as ID Inline

[RepairID,CarPlateID

1001,MAZ1

1002,MAZ2

1003,MAZ3

1004,MAZ4

1005,MAZ5

1006,MAZ6

1007,MAZ7

];

Table2:

Load * Inline

[RepairID

1006

1007

1008

1009

1010

1011

];

Inner join (Table1)

Test1:

Load RepairID as Common_RepaireID Resident Table2 where Exists(ID,RepairID);