topic Re: Three dates in one table in QlikView

Three dates in one table

priya945 — Sat, 16 Nov 2024 19:22:46 GMT

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.

Re: Three dates in one table

Frank_Hartmann — Sat, 28 Dec 2019 22:01:52 GMT

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;

Re: Three dates in one table

priya945 — Sun, 29 Dec 2019 05:21:09 GMT

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

Re: Three dates in one table

Frank_Hartmann — Sun, 29 Dec 2019 19:08:45 GMT

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

Re: Three dates in one table

priya945 — Mon, 30 Dec 2019 08:45:57 GMT

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

Re: Three dates in one table

Brett_Bleess — Mon, 30 Dec 2019 14:19:40 GMT

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

Re: Three dates in one table

priya945 — Tue, 31 Dec 2019 12:04:29 GMT

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.

Re: Three dates in one table

priya945 — Wed, 01 Jan 2020 07:57:14 GMT

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

Re: Three dates in one table

priya945 — Sun, 05 Jan 2020 08:23:25 GMT

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 ?