topic Re: Three dates into one date in QlikView

Three dates into one date

Thu, 04 Feb 2016 20:34:00 GMT

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,

Re: Three dates into one date

oknotsen — Thu, 04 Feb 2016 20:43:09 GMT

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?

Re: Three dates into one date

Thu, 04 Feb 2016 21:13:46 GMT

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.

Thanks,

Re: Three dates into one date

oknotsen — Thu, 04 Feb 2016 21:42:17 GMT

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.

Re: Three dates into one date

oknotsen — Thu, 04 Feb 2016 21:44:58 GMT

Also, change this line:

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

to this line:

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

Re: Three dates into one date

MarcoWedel — Thu, 04 Feb 2016 22:02:43 GMT

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

regards

Marco

Re: Three dates into one date

MarcoWedel — Thu, 04 Feb 2016 22:11:45 GMT

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

Re: Three dates into one date

Thu, 04 Feb 2016 22:25:20 GMT

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;

Re: Three dates into one date

MarcoWedel — Thu, 04 Feb 2016 22:44:27 GMT

define "doesn't work"

Re: Three dates into one date

MarcoWedel — Thu, 04 Feb 2016 22:50:36 GMT

seems to work for me:

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

Re: Three dates into one date

Fri, 05 Feb 2016 15:43:30 GMT

Thanks Onno Van and Marco for your help and time. It was an issue at the qualify and unqualify level since I am dealing with multiple QVD tables.

Re: Three dates into one date

oknotsen — Sat, 06 Feb 2016 14:56:14 GMT

Qualify? Classic !

Not sure what qualify and multiple QVD tables have to do with each other.