Match multiple value in 2 tables

[jimbo_carlos]

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

[isingh30]

Use Lookup function

[Anonymous]

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!

[vishsaggi]

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().

[jimbo_carlos]

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

[qlikviewwizard]

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);