Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
New Contributor

How to find the matching and non matching records from 2 tables

Hello All,

I have 2 tables assume table A and B in which id is the common filed, I need to compare table A id with B and need to get the non matching records count and matching records count.

Also in table B i have a status column for the id from table A i may have 2 or 3 transaction status and transaction numbers if all the status are passes then that particular should come in passed category if not it should come in failed category in the pivot or straight table created.

Let me give you a sample data

Table 1 :

ID      owner

100     A

102    B

103    C

401     D

Table 2:

ID     transid   status

100    1           pass

100    2           pass

102    5           failed

102    8           pass

103    4           failed

Now i need to get the output as

count of passed ids = 1 (100 is the ID)

Count of failed ids  = 2 (102 and 103)

count of not executed as = 1 ( 401)

Please help me in this.

Thanks in advance

1 Solution

Accepted Solutions
Highlighted
Valued Contributor

Re: How to find the matching and non matching records from 2 tables

Oh, a little correction

View solution in original post

10 Replies
Highlighted
Valued Contributor

Re: How to find the matching and non matching records from 2 tables

Hi Haritha. See attached

Partner
Partner

Re: How to find the matching and non matching records from 2 tables

Hi,

Perhaps look at the match function or wildmatch with a where clause in your backend script

Highlighted
New Contributor

Re: How to find the matching and non matching records from 2 tables

Hi Adrey,

Thanks for you quick response, it helped me

Highlighted
New Contributor

Re: How to find the matching and non matching records from 2 tables

Thank you Thabang , i will go through.

Highlighted
Valued Contributor III

Re: How to find the matching and non matching records from 2 tables

I would do something like this:

Header:

LOAD * INLINE [

    ID, Owner

    100, A

    110, B

    111, A

];

Left Join (Header)

LOAD * INLINE [

    ID, Status

    100, pass

    110, failed

];

NoConcatenate

tmp:

LOAD ID

,Owner

,If(Len(Trim(Status)) = 0, 'not executed', Status) as Status

Resident Header;

DROP Table Header;


In the front-end use Text objects:

=Count({<Status = {'pass'}>} ID)

=Count({<Status = {'failed'}>} ID)

=Count({<Status = {'not executed'}>} ID)

Highlighted
Valued Contributor

Re: How to find the matching and non matching records from 2 tables

Oh, a little correction

View solution in original post

Highlighted
New Contributor

Re: How to find the matching and non matching records from 2 tables

Thank you Andrey,

I Have added some data , now i am expecting to get the pass count as 0 but i am getting it as 1.

. What exactly i want is for a id if any transaction got failed then that id should come under failed category and we need to take the count of it and same id should not be considered in pass count.

Thanks a lot in advance.

Please find the attachment for the same.

Highlighted
Valued Contributor

Re: How to find the matching and non matching records from 2 tables

Yea, some more changes...

Highlighted
Valued Contributor

Re: How to find the matching and non matching records from 2 tables

And more, Haritha, if it will be non inline load and there will be null values ​​in transid or owner fields, the logic of matching and non matching values will not work.