topic Date Shift Problem in QlikView

Date Shift Problem

Fri, 15 Apr 2011 11:59:47 GMT

Dear All,

I have a file which shows holidays for employees. The data field format is as below:

1001M01

First two character presents years
Second two character presents months
Remaining part of the field is not important

When the employee has an holiday at December it shows in next month as January in the system. I would like to show it in exact date as December. How can I make this shift.

Date Shift Problem

erichshiino — Fri, 15 Apr 2011 12:26:44 GMT

Hi,

First, we need to transform your string into a date.

=makedate(left(string,2)+2000, num(mid(string,3,2)))

Second, we shift this date to the previos month.

= addmonsths( makedate(left(string,2)+2000, num(mid(string,3,2))) , -1)

Rgds,

Date Shift Problem

Fri, 15 Apr 2011 13:15:25 GMT

Hi Erich,

Thanks for you solution. It works for years which are greater than 2000. There are also some information for 1999, 1998, 1997, etc. In above solution those years look like as 2099, 2098, 2097. What do you suggest for this?

Kind Regards,

Date Shift Problem

chris_johnson — Fri, 15 Apr 2011 15:00:03 GMT

Hi,

I suppose you would need to set a point at which the birthdays are either in the 1900's or the 2000's. For example, if you say that anyone with a birthday after the year 20 is going to be in the 1900's then:

= if (left(string,2) <= 20, addmonsths( makedate(left(string,2)+2000, num(mid(string,3,2))) , -1),

addmonsths( makedate(left(string,2)+1900, num(mid(string,3,2))) , -1))

I'm sure there's probably a way to make a check on the current year to see if the birth year further forward than the current year, maybe:

= if(left(string,2) <= year(today(0), addmonsths( makedate(left(string,2)+2000, num(mid(string,3,2))) , -1),

addmonsths( makedate(left(string,2)+1900, num(mid(string,3,2))) , -1))

Chris

Date Shift Problem

Miguel_Angel_Baeyens — Fri, 15 Apr 2011 15:16:47 GMT

Hi, I agree with Chris, there must be in some point where you have to say if it's 20th or 21st century.

But for the time being, taking a loot at your data, the following should be enough to transform field CJ into Date

=Date(Date#(Left(CJ, 4), 'YYMM'))

Those that start in 99 will be considered as 1999, those that start in 00 or greater will be considered as 2000.

Hope that helps.