topic Re: Identifying non-matched Rows - Interesting Case in QlikView

Identifying non-matched Rows - Interesting Case

Anonymous — Wed, 08 Mar 2017 18:18:32 GMT

Hi Experts!

I have three different tables. All containing Date and ID. I want to create a new table that should have Date Type, Date and ID. In Date Type I want to list only those dates associate with that type table.

For example let me show you the output I'm after:

Basically for type A I want all the dates and ID in Type A table. for Type B, I want to show only those date which start after the end date of Table A. And for Type C, I want to show only those dates which after after the end date of Table B.

is this something we can do in Qlik?

Thanks

Re: Identifying non-matched Rows - Interesting Case

Anonymous — Wed, 08 Mar 2017 18:20:00 GMT

I'm attaching the excel file containing this data if anyone would like to have a closer look at the data. Thanks

Re: Identifying non-matched Rows - Interesting Case

aarkay29 — Wed, 08 Mar 2017 18:39:32 GMT

Try this

LOAD

[Date A] as Date,

[ID A] as ID,

'A' As TableID

FROM

Table A;

Concatenate

LOAD [Date B] as Date,

[ID B] as ID,

'B' as TableID

FROM

Table B

Where

Not Exists(Date);

Concatenate

LOAD [Date C] as Date,

[ID C] as ID,

'C' as TableID

FROM

Table C

Where

Not Exists (Date);

Re: Identifying non-matched Rows - Interesting Case

Anonymous — Wed, 08 Mar 2017 18:40:03 GMT

TABLE:
LOAD [Date A] as Datefield,
[ID A] as ID,
'A' as Type
FROM
[Data v5.xlsx]
(ooxml, embedded labels, table is Sheet1);

TEMP: LOAD max(Datefield) as maxdate Resident TABLE;
let max=Peek('maxdate');

Concatenate(TABLE)

LOAD [Date B] as Datefield,
[ID B] as ID,
'B' as Type
FROM
[Data v5.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where [Date B]> $(max);

TEMP: LOAD max(Datefield) as maxdate Resident TABLE;
let max=Peek('maxdate');

Concatenate(TABLE)

LOAD [Date C] as Datefield,
[ID C] as ID,
'C' as Type
FROM
[Data v5.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where [Date C]> $(max);

DROP Table TEMP;

Re: Identifying non-matched Rows - Interesting Case

maxgro — Wed, 08 Mar 2017 18:49:33 GMT

LOAD

'A' as [Date Type],

[Date A] as Date,

[ID A] as ID

FROM [Data v5.xlsx] (ooxml, embedded labels, table is Sheet1);

Max:

load max(Date) as MaxDate Resident X;

LET vMaxDate = Peek('MaxDate');

DROP Table Max;

LOAD

'B' as [Date Type],

[Date B] as Date,

[ID B] as ID

FROM [Data v5.xlsx] (ooxml, embedded labels, table is Sheet1)

WHERE [Date B] > $(vMaxDate);

Max:

load max(Date) as MaxDate Resident X;

LET vMaxDate = Peek('MaxDate');

DROP Table Max;

LOAD

'C' as [Date Type],

[Date C] as Date,

[ID C] as ID

FROM [Data v5.xlsx] (ooxml, embedded labels, table is Sheet1)

WHERE [Date C] > $(vMaxDate);

Re: Identifying non-matched Rows - Interesting Case

sunny_talwar — Wed, 08 Mar 2017 18:58:56 GMT

Another method

Table:

LOAD Num([Date A]) & [ID A] as Key,

[Date A] as Date,

[ID A] as ID,

'A' as [Date Type]

FROM

[..\..\Downloads\Data v5.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim([Date A])) > 0;

Concatenate (Table)

LOAD Num([Date B]) & [ID B] as Key,

[Date B] as Date,

[ID B] as ID,

'B' as [Date Type]

FROM

[..\..\Downloads\Data v5.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where not Exists(Key, Num([Date B]) & [ID B]) and Len(Trim([Date B])) > 0;

Concatenate (Table)

LOAD Num([Date C]) & [ID C] as Key,

[Date C] as Date,

[ID C] as ID,

'C' as [Date Type]

FROM

[..\..\Downloads\Data v5.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where not Exists(Key, Num([Date C]) & [ID C]) and Len(Trim([Date C])) > 0;

Re: Identifying non-matched Rows - Interesting Case

maxgro — Wed, 08 Mar 2017 19:10:35 GMT

I think the "not exists" option only works if there aren't missing dates

01/01/2015

02/01/2015

03/01/2015

05/01/2015

03/01/2015

04/01/2015

05/01/2015

06/01/2015