Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I'm attaching the excel file containing this data if anyone would like to have a closer look at the data. Thanks
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);
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;
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);
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;
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