topic Re: Trouble with date format in QlikView

Trouble with date format

Tue, 17 Jan 2012 12:14:00 GMT

I have the following load on one dashboard

SALES:Directory;QUALIFY *;UNQUALIFY ControlDate,;

LOAD Status,

.....

[Decision Date] as [Global Award Date],

[Phase Since],

....

if(Status = 'Open', [Decision Date],
if(Status = 'Won', [Decision Date],
if(Status = 'Lost', [Decision Date],date([Phase Since])))) as ControlDate FROM [$(QVDPath)Sales.xlsx] (ooxml, embedded labels, table is SAPBW_DOWNLOAD);

I have a second load on a different dashboard:

Revenue:Directory;QUALIFY *;UNQUALIFY ControlDate;

LOAD [Opportunity ID],

.....

[Decision Date],

[Phase Since],

....

if(Status = 'Open', date([Decision Date]),
         if(Status = 'Won', date([Decision Date]),
            if(Status = 'Lost', date([Decision Date]),date([Phase Since])))) as ControlDate
         FROM

(ooxml, embedded labels, table is SAPBW_DOWNLOAD);

I have a Calendar:

Range:LOAD
num(date('01/04/2008')) as startdate, //######## CHANGE HERE ######## max(ControlDate) as enddate //######## CHANGE HERE ########resident SALES; //######## CHANGE HERE ########

//Peek out the values for later uselet vStart = peek('Range.startdate',-1,'Range')-1;let vEnd = peek('Range.enddate',-1,'Range');let vRange = $(vEnd) - $(vStart);//Remove Range table as no longer neededDrop table Range;
//Generate a table with a row per date between the range aboveDate:UNQUALIFY *;Load
$(vStart)+recno() as Dateautogenerate $(vRange);
//Calculate the Parts you need to examineDateParts:load
Date as [ControlDate], //######## CHANGE HERE ######## date(Date,'dd/mm/yyyy') as FullDate,

.......

resident Date;

I used the Calendar on both dashboards ( but I change the resident from SALES to Revenue)

The first version works but the second does not.

With the second version the control date comes out like 01/01/2011 rather than in the first dashboard which is like 39541 (which I assume is a Julian date)

The input fields Decision date and Phase Since are exactly the same on both of the source spreadsheets (Format General). I tried date([Decision Date]) on the second dashboard but to no effect.

Does anyone have a suggestion where the differenvce might lie?

Regards

Jason

Re: Trouble with date format

Miguel_Angel_Baeyens — Tue, 17 Jan 2012 12:24:20 GMT

Hi Jason,

It's indeed a Julian date, since Max() and Min() return only numeric values (as dates are). Try changing this line

$(vStart)+recno() as Date

To this one

Date($(vStart) + RecNo()) as Date

In your calendar table, so the result is a formatted date like "17/01/2012" (or whatever format according to your local OS settings).

Hope that helps.

Re: Trouble with date format

Tue, 17 Jan 2012 12:32:35 GMT

Hi Miguel

Thanks for your help. I have changed the Calendar to the following:

UNQUALIFY *;

Load

// $(vStart)+recno() as Date

Date($(vStart) + RecNo()) as Date

autogenerate $(vRange);

Unfortunatley the ControlDate is still displayed like - 02/01/2003. Any other thoughts?

Regards

Jason

Re: Trouble with date format

swuehl — Tue, 17 Jan 2012 12:36:27 GMT

I would check three things:

1) Are your dates correctly read in as date types (with numerical representation)? If not, check the source format and use date#() or timestamp#() function to read your dates in

2) Check that your format your dates consistently using date() function. It seems that you used date() function on second, but not on first excel table load to format your decision date

3) Check your date format codes: In the last date function, you used

date(Date,'dd/mm/yyyy') as FullDate,

IMHO, 'dd/mm/yyyy' should be replaced by 'DD/MM/YYYY' , at least 'mm' is indicating minutes, not Months, so you will get zero values in the middle for all records, if you are handling Dates (not timestamps).

Hope this helps,

Stefan

edit:

If you don't want your dates to be shown as 17/01/2012, but with a numerical instead, use num() function to display your ControlDates as numericals.

Re: Trouble with date format

Tue, 17 Jan 2012 12:56:25 GMT

Stefan,

Thanks the DD/MM/YYYY corrects the error I was have with the Full date.

The ControlDate is still coming out with 01/02/2003 whether I use Date#() on the intial load of the value or in the IF statement.

The date in the spreadsheet has a format of General , so it is not numeric. What is confusing me is that the data in the past was Generl and the Calendar derived a Julian date!

Regards

Jason

Trouble with date format

vijay_iitkgp — Tue, 17 Jan 2012 13:06:32 GMT

Hi Jason,

My solution is little strange but it will work for sure.

You can use Num(makedate(Right(Date,4),mid(Date,4,2),left(Date,2)) as Date

Hope this will help

Trouble with date format

Wed, 18 Jan 2012 08:48:21 GMT

Vijay,

I have tried your fix,but I am still getting 01/01/2012 rather than a Julian date.

Thanks for your response

Jason

Re: Trouble with date format

swuehl — Wed, 18 Jan 2012 11:22:26 GMT

Even if you apply num() function to your current Date field (in an object expression or in the script)?

Please check that your date field is not just a text / string. I don't think it is, because I think your standard date format should have taken care of it (you are using 'DD/MM/YYYY' as DateFormat, right?), but if you have a mix of date formats, this is sometime the root cause - some of your imported fields may only display a text, that looks like a date, but it is not recognized as date type with also a numerical representation.

But first try with num() function.

Hope this helps,

Stefan

Re: Trouble with date format

Wed, 18 Jan 2012 12:14:18 GMT

Stefan,

The dates I am bringing in from the Excel spreadsheet have a format of general (so I assume that menas they are strings) are in MM/DD/YYYY form.

I have tried Vijay's num(makedate(....)) in the load script as follows:

if(Status = 'Open', date(Num((makedate(Right([Decision Date],4),left([Decision Date],2),mid([Decision Date],4,2))))),
if(Status = 'Won', date(Num((makedate(Right([Decision Date],4),left([Decision Date],2),mid([Decision Date],4,2))))),
if(Status = 'Lost', date(Num((makedate(Right([Decision Date],4),left([Decision Date],2),mid([Decision Date],4,2))))),
date(Num((makedate(Right([Phase Since],4),left([Phase Since],2),mid([Phase Since],4,2)))))))) as ControlDate

The control date is then used in the Calendar as above

- except with DD/MM/YYYY for Full Date

I have tried both :

$(vStart)+recno() as Date

And Date($(vStart) + RecNo()) as Date

for the autogenerate of $(vRange) in the calendar, but both result in the Control Date being in the form DD/MM/YYYY and not a Julian date.

Thanks once again for your assistance.

Jason

Trouble with date format

vijay_iitkgp — Wed, 18 Jan 2012 12:33:34 GMT

Hi Jason,

Please do not use date before num. It is again converting num to date

Trouble with date format

Wed, 18 Jan 2012 12:38:43 GMT

Vijay,

Thanks it is now a Julian date. That really was rather a stupid mistake to make..... sorry!

Thanks for your help and thank you to Stefan and Miguel.

Regards

Jason