Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Three dates into one date

Hi All,

I have to merge three dates into one date: order date, start date, and finish date and I have to merge all of these three dates into one date called the implementation date. I have been suggested master calender and canonical date. I am trying to use master calendar but it keeps running into an error.

Temp: 

Load 

               min(orderdate) as minDate, 

               max(orderdate) as maxDate 

Resident mytable; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

orderdateCalendar: 

Load 

               TempDate AS orderdate

               week(TempDate) As orderdateWeek, 

               Year(TempDate) As orderdateYear, 

               Month(TempDate) As orderdateMonth, 

               Day(TempDate) As orderdateDay, 

               YeartoDate(TempDate)*-1 as orderdateCurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 asorderdateLastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as orderdateRC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as orderdateMonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as orderdateQuarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as orderdateWeekYear, 

               WeekDay(TempDate) as orderdateWeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

Thanks in advance,

1 Solution

Accepted Solutions
MarcoWedel

seems to work for me:

QlikCommunity_Thread_204309_Pic1.JPG

d2:

LOAD Date(Today()-Ceil(Rand()*1000)) as linestartdate

AutoGenerate 100;

linestartdateCalendar:

Load          linestartdate,

              week(linestartdate) As linestartdateWeek,

              Year(linestartdate) As linestartdateYear,

              Month(linestartdate) As linestartdateMonth,

              Day(linestartdate) As linestartdateDay,

              YeartoDate(linestartdate)*-1 as linestartdateCurYTDFlag,

              YeartoDate(linestartdate,-1)*-1 as linestartdateLastYTDFlag,

              date(monthstart(linestartdate), 'MMM-YYYY') as linestartdateMonthYear,

              Dual('Q'&Ceil(Month(linestartdate)/3),Ceil(Month(linestartdate)/3)) as linestartdateQuarter,

              WeekName(linestartdate) as linestartdateWeekYear,

              WeekDay(linestartdate) as linestartdateWeekDay;

LOAD      Date(minDate + IterNo() - 1) as linestartdate

While      minDate + IterNo() - 1 <= maxDate;

Load        min(linestartdate) as minDate,

              max(linestartdate) as maxDate

Resident d2;

please post an example where it doesn't work.

thanks

regards

Marco

View solution in original post

11 Replies
oknotsen
Master III
Master III

What is the exact error you are getting?

Does your mytable table actually have an orderdate?

Is it spelled orderdate, OrderDate or Orderdate?

Is your mytable spelled mytable or myTable?

Did you floor() that date field?

May you live in interesting times!
Not applicable
Author

date(orderdate) as orderdate

Yes mytable has an orderdate but it shows up as an integer so I have to convert it into a date. It is spelled orderdate and mytable. How would I use the floor () function for this scenario?

Also find attached my errors.

Capture.PNGCapture1.PNGCapture2.PNGCapture3.PNG

Thanks,

oknotsen
Master III
Master III

Based on the first error, your "mytable" table seems not to exist. Check if you are spelling that name 100% correct. Please note case sensitivity.

All other errors are the result of the first error.

A date is a number. No need to change the looks of it to a date (as that is that date() does; just formatting).

To floor that thing, type this:

floor(orderdate) as orderdate

But again, the problem is that your "mytable" does not exists, most probably due to it having a different name.

May you live in interesting times!
oknotsen
Master III
Master III

Also, change this line:

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

to this line:

AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

May you live in interesting times!
MarcoWedel

in this case also use RecNo() instead of IterNo() in the rest of the TempCalendar Load.

regards

Marco

MarcoWedel

another version of a calendar script:

orderdateCalendar:

Load        orderdate,

              week(orderdate) As orderdateWeek,

              Year(orderdate) As orderdateYear,

              Month(orderdate) As orderdateMonth,

              Day(orderdate) As orderdateDay,

              YeartoDate(orderdate)*-1 as orderdateCurYTDFlag,

              YeartoDate(orderdate,-1)*-1 asorderdateLastYTDFlag,

              date(monthstart(orderdate), 'MMM-YYYY') as orderdateMonthYear,

              Dual('Q'&Ceil(Month(orderdate)/3),Ceil(Month(orderdate)/3)) as orderdateQuarter,

              WeekName(orderdate) as orderdateWeekYear,

              WeekDay(orderdate) as orderdateWeekDay;

LOAD      Date(minDate + IterNo() - 1) as orderdate

While      minDate + IterNo() - 1 <= maxDate;

Load        min(orderdate) as minDate,

              max(orderdate) as maxDate

Resident mytable;

doesn't solve your issue with trying to load resident from table "mytable" that doesn't exist at this point of your script ...

hope this helps

regards

Marco

Not applicable
Author

Hi Marco,

The below link doesn't work. This is for another date instead of the order date and it is in another table.

Field Name : linestartdate

Table Name: d2

linestartdateCalendar:

Load          linestartdate, 

              week(linestartdate) As linestartdateWeek, 

              Year(linestartdate) As linestartdateYear, 

              Month(linestartdate) As linestartdateMonth, 

              Day(linestartdate) As linestartdateDay, 

              YeartoDate(linestartdate)*-1 as linestartdateCurYTDFlag, 

              YeartoDate(linestartdate,-1)*-1 as linestartdateLastYTDFlag, 

              date(monthstart(linestartdate), 'MMM-YYYY') as linestartdateMonthYear, 

              Dual('Q'&Ceil(Month(linestartdate)/3),Ceil(Month(linestartdate)/3)) as linestartdateQuarter, 

              WeekName(linestartdate) as linestartdateWeekYear, 

              WeekDay(linestartdate) as linestartdateWeekDay; 

LOAD      Date(minDate + IterNo() - 1) as linestartdate 

While      minDate + IterNo() - 1 <= maxDate; 

Load        min(linestartdate) as minDate, 

              max(linestartdate) as maxDate 

Resident d2;

MarcoWedel

define "doesn't work"

MarcoWedel

seems to work for me:

QlikCommunity_Thread_204309_Pic1.JPG

d2:

LOAD Date(Today()-Ceil(Rand()*1000)) as linestartdate

AutoGenerate 100;

linestartdateCalendar:

Load          linestartdate,

              week(linestartdate) As linestartdateWeek,

              Year(linestartdate) As linestartdateYear,

              Month(linestartdate) As linestartdateMonth,

              Day(linestartdate) As linestartdateDay,

              YeartoDate(linestartdate)*-1 as linestartdateCurYTDFlag,

              YeartoDate(linestartdate,-1)*-1 as linestartdateLastYTDFlag,

              date(monthstart(linestartdate), 'MMM-YYYY') as linestartdateMonthYear,

              Dual('Q'&Ceil(Month(linestartdate)/3),Ceil(Month(linestartdate)/3)) as linestartdateQuarter,

              WeekName(linestartdate) as linestartdateWeekYear,

              WeekDay(linestartdate) as linestartdateWeekDay;

LOAD      Date(minDate + IterNo() - 1) as linestartdate

While      minDate + IterNo() - 1 <= maxDate;

Load        min(linestartdate) as minDate,

              max(linestartdate) as maxDate

Resident d2;

please post an example where it doesn't work.

thanks

regards

Marco