Three dates in one table

[priya945]

Hi All,

I have below data with 3 dates. I am unable to use the canonical date as i don't have the single common field for 3 dates. so please suggest how can i handle these dates in data Modelling  and restrict the dates last 2 dates(ex:9/30/2018,10/31/2018)

Case Number	Region	Closed Date	Opened Date	8/31/2018	9/30/2018	10/31/2018
1234	North	30-08-2018	30-08-2018	1	1	0
1243	North	30-09-2018	30-09-2018	0	1	0
1245	South	31-10-2018	31-10-2018	1	0	1
1254	South	30-08-2018	30-08-2018	1	1	1
1123	East	30-09-2018	31-10-2018	0	1	0
1232	West	31-10-2018	31-10-2018	0	0	0

Thanks in advance.

[Frank_Hartmann]

try this:

tmp1:
CrossTable(Date, Data, 4)
LOAD *
FROM [https://community.qlik.com/t5/New-to-QlikView/Three-dates-in-one-table/td-p/1661200]
(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

final:
Load * Resident tmp1 Where autonumber(Date)>=2; DROP table tmp1;

[priya945]

Thanks for your reply

But i need a common date field for 3 dates(Closed Date, Opened Date and (8/31/2018 9/30/2018 10/31/2018) after cross function so that i can filter the data with common date field

Thanks in advance 

[Frank_Hartmann]

i think you should consider using master calendar and connecting all 3 datefields to this  calendartable. then using the mastercalenderdatefield for filtering.

 https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

[priya945]

Hi Frank,

Thanks for your reply, But 

1. i can't use single master calendar for 3 dates 

2. Can't create 3 master calendar as 3 dates should be shown in single pivot table    

3. Can't create canonical date as i don't have single common field for 3 dates

Still i need create a common calendar for 3 dates to filter data. please suggest. Expected o/p is shown in attachment 

Thanks in advance 

[Brett_Bleess]

Check the following Design Blog post, I think you are misunderstanding the Canonical Date use case...

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Regards,
Brett

[priya945]

From the link

"First you must find a table with a grain fine enough; a table where each record only has one value of each date type associated. In my example this would be the OrderLines table, since a specific order line uniquely defines all three dates. "

My data doesn't have a single common filed for 3 dates. so canonical date concept is not working. please let me know if still this works for me

Thanks in advance.

[priya945 · Accepted solution]

i managed to get the solution by creating a flag and master calendar

[priya945]

Hi,

Test:

load * Inline [

Case Number, Region , Closed Date, Opened Date
1234, North, 30-08-2018, 30-08-2018
1243, North, 30-09-2018, 30-09-2018
1245, South, 31-10-2018, 31-10-2018
1254, South, 30-08-2018, 30-08-2018
1123, East, 30-09-2018, 31-10-2018
1232, West, 31-10-2018, 31-10-2018

];

close:

load
[Case Number],
Region,
Date(Date#([Opened Date],'DD-MM-YYYY'),'M/D/YYYY') as NDate,
[Closed Date],
[Opened Date],
'Close' as Flag
Resident Test;

Concatenate

load
[Case Number],
Region,
[Closed Date],
[Opened Date],
Date(Date#([Opened Date],'DD-MM-YYYY'),'M/D/YYYY') as NDate,
'Open' as Flag
Resident Test;

drop Table Test;

DateRange:
Load
min(Date(NDate),'M/D/YYYY') as MinDate,
max(Date(NDate),'M/D/YYYY') as MaxDate
Resident close;

Let varMinDate = num(Peek('MinDate',0,'DateRange'));
Let varMaxDate = num(Peek('MaxDate',0,'DateRange'));

TempCalendar:
LOAD
date($(varMinDate)+RowNo()-1) AS TempDate
AUTOGENERATE $(varMaxDate)- $(varMinDate) + 1 ;

MasterCalendar:
Load
TempDate As NDate,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month
resident TempCalendar Where Exists([Opened Date],TempDate) ;

DROP Table TempCalendar;
DROP Table DateRange;

From above script, NDate is the key between two tables. Can i use the NDate(whch is keyfield) in the front end expressions/as a dimension ?