Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

common date

Newbie needing help here.. been trying to solve this for days.. using the canonical dates tutorial..

mine is different though as all of the dates should show up the same

I have 4 dates

Fin_Month

Cust_Month

Snapshot_Month

People_Month

tried to create a tempcal and master calendar.. but its isolated from the rest of my data

i would like to just select Snapshot Month and it should automatically select the same months as well

RepMonthSnapshot MonthCust_MonthFin_Month
31-Dec-1412/31/201412/31/201412/31/2014
31-Jan-1501/31/201501/31/201501/31/2015
28-Feb-1502/28/201502/28/201502/28/2015
31-Mar-1503/31/201503/31/201503/31/2015
30-Apr-1504/30/201504/30/201504/30/2015
31-May-1505/31/201505/31/201505/31/2015
30-Jun-1506/30/201506/30/201506/30/2015
31-Jul-1507/31/201507/31/201507/31/2015
31-Aug-1508/31/201508/31/201508/31/2015
30-Sep-1509/30/201509/30/201509/30/2015
31-Oct-1510/31/201510/31/201510/31/2015
30-Nov-1511/30/201511/30/201511/30/2015
31-Dec-1512/31/201512/31/201512/31/2015
23 Replies
jpenuliar
Partner
Partner

Third Table is not linked to the Bridge,

Another trick is  a "Date Island" which you can use,

But I prefer the rwunderlich technique.

Not applicable
Author

hi rob.. sorry.. this is so similar to reading a map (girls find it hard to read maps).. so we just ask .. ive ben through all the tutorials for several weeks.. and still getting nowhere..

anyway.. tried to create a datelink

datelink:

Load
LT,
Fin_Month as CommonDate,
'Common'
as DateType
Resident financials;

no errors on this one..

however for Snapshot Mont

should it be 'Common' as DateType as well?

Not applicable
Author

here is what I have now.. I now have duplicate dates

tempcal:

Load
Date([Snapshot Month]) as CommonDate
Resident weekly_outstanding;
Concatenate
Load
Date(Fin_Month) as CommonDate
Resident financials;



Temp:
LOAD
Min(CommonDate) as MinDate,
Max(CommonDate) as MaxDate
Resident tempcal;

Let varMinDate = Num(Peek('MinDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('MaxDate', 0, 'Temp'));

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

mastercalendar:
Load
Date(monthend(TempDate),'YY-MMM-DD') as CommonDate,
Year(TempDate) as CommonYear,
Month(TempDate) as CommonMonth
Resident TempCalendar;
DROP Table TempCalendar;

datelink:
Load
LT,
Fin_Month as CommonDate,
'Financial'
as DateType
Resident financials;
Load
LT,
Fin_Month as CommonDate,
'Risk'
as DateType
Resident financials;

Fin_MonthSnapshot MonthCommonDate
12/31/201401/31/201502/28/2015
12/31/201402/28/201502/28/2015
12/31/2014 02/28/2015
01/31/201501/31/201502/28/2015
01/31/201502/28/201502/28/2015
01/31/2015 02/28/2015
02/28/201501/31/201502/28/2015
02/28/201502/28/201502/28/2015
02/28/2015 02/28/2015

jpenuliar
Partner
Partner

Hi Lanie,

I noticed that all values in your Cust_Month,Fin_Month and Snapshop Month are all the same dates.

The solution I came up with is to make an Alias of all 3 fields in their respective Load script.

Cust_Month as Date

Fin_Month as Date

[Snapshot Month] as Date

This should work, otherwise, you will have to consider reviewing your Data Model