Identifying non-matched Rows - Interesting Case

[Anonymous]

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

[Anonymous]

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

[aarkay29]

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

[Anonymous]

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;

[maxgro]

X:

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

[sunny_talwar]

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;

[maxgro]

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

A

01/01/2015

02/01/2015

03/01/2015

05/01/2015

B

03/01/2015

04/01/2015

05/01/2015

06/01/2015