topic Re: Identify None Matching Data in QlikView

Identify None Matching Data

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

Hi,

I have several joined tables One master table and others which I use the data from too.

Table 1:

ID Colour

1 Red

2 Orange

3 Purple

4 Blue

5 Brown

Left Join

Table 2:

ID Name

1 John

5 Jane

I have many other columns of data and use a left join as I use data in both tables. I want to identify, the amount of data in table 2 which is not in table 1. i.e. identify that 2,3,4 are not in table 2 but in Table1 so 3 fields are missing would be my end goal here.

Thanks

Re: Identify None Matching Data

Chanty4u — Thu, 16 Feb 2017 09:46:58 GMT

AM not sure but

table:

LOAD * INLINE [

ID , Colour

1 , Red

2 , Orange

3 , Purple

4 , Blue

5 ,Brown

];

Left Join

Table2:

LOAD * INLINE [

ID , Name

1 , John

5 , Jane

]

Where not Exists(ID) ;

Re: Identify None Matching Data

MayilVahanan — Thu, 16 Feb 2017 09:48:00 GMT

Try like this

Table1:

LOAD * INLINE [

ID,Colour

1,Red

2,Orange

3,Purple

4,Blue

5,Brown

];

Table2:

LOAD * Inline

[

ID,Name

1, John

5,Jane

];

Left Join(Table1)

LOAD ID, 1 as flag Resident Table2;

Table3:

LOAD ID as MissingID Resident Table1 where flag <> 1;

Re: Identify None Matching Data

sunny_talwar — Thu, 16 Feb 2017 09:49:56 GMT

Create a flag while doing a left join

Table1:

LOAD ...

FROM Table1

Left Join (Table1)

LOAD *,

1 as FlagTemp

FROM Table2;

Now you can create this:

FinalTable:

LOAD *,

If(FlagTemp = 1, 1, 0) as Flag

Resident Table1;

DROP Table Table1;

DROP Field FlagTemp;

Now you have you flag where 1 is data available in Table2 and 0 where data isn't available.

Re: Identify None Matching Data

alwayslearning — Thu, 16 Feb 2017 11:10:32 GMT

This has worked great.

Thanks everyone for your help