Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

Three dates in one table

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 NumberRegionClosed DateOpened Date8/31/20189/30/201810/31/2018
1234North30-08-201830-08-2018110
1243North30-09-201830-09-2018010
1245South31-10-201831-10-2018101
1254South30-08-201830-08-2018111
1123East30-09-201831-10-2018010
1232West31-10-201831-10-2018000

 

Thanks in advance.

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Contributor

Re: Three dates in one table

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

View solution in original post

8 Replies
Highlighted
Honored Contributor II

Re: Three dates in one table

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;
Highlighted
Contributor

Re: Three dates in one table

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 

 

 

Highlighted
Honored Contributor II

Re: Three dates in one table

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

Highlighted
Contributor

Re: Three dates in one table

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 

 
 
Highlighted
Digital Support
Digital Support

Re: Three dates in one table

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Contributor

Re: Three dates in one table

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.

Highlighted
Contributor

Re: Three dates in one table

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

View solution in original post

Highlighted
Contributor

Re: Three dates in one table

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 ?