topic Date (time) format challenge in QlikView

Date (time) format challenge

Anonymous — Tue, 21 Feb 2012 10:37:19 GMT

Dear specialist,

I got a format challenge.

The datefield from my data ocures in different formats.

Obvoius I need them to be all the same.

Here are some sample of all the formats that are in my data

Date	Time format info:
13:35pm	Today
Yesterday 10:25am	Yesterday
18 Feb 2:01pm	Dates this year
3 Jan 11:02am	Dates this year
31 Nov 2011	Dates previous year (and older)

I can not change the format directly in my data because it is loaded from a website, so I need to solve this in the loading script.

Any suggestions?

Dennis.

Date (time) format challenge

jagan — Tue, 21 Feb 2012 10:39:11 GMT

Hi,

Try using the Alt(). Hope it helps you.

Regards,

Jagan.

Date (time) format challenge

Anonymous — Tue, 21 Feb 2012 11:10:48 GMT

Hi Jagan,

Thanks for your respond, but can you give me an example of the load script bases on the Excel file I atteched?

Becasue it doesn't make sence to me.

Thanks,

Dennis.

Re: Date (time) format challenge

Anonymous — Tue, 21 Feb 2012 11:31:25 GMT

I am almost there with this script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrt;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

Let vCurrentYear = Year(today());

Let vToday = Date(today());

DateFormat:

LOAD

Replace

(Replace

(Datum ,

' Jan ' , '-01-'),

' Feb ' , '-02-'),

' Mar ' , '-03-'),

' Apr ' , '-04-'),

' May ' , '-05-'),

' Jun ' , '-06-'),

' Jul ' , '-07-'),

' Aug ' , '-08-'),

' Sep ' , '-09-'),

' Oct ' , '-10-'),

' Nov ' , '-11-'),

' Dec ' , '-12-')

as Datum,

Datum as DateAfterLoad1,

Date as OrgDateFormat;

LOAD

IF (Right(Date, 1) <> 'm' , Date ,

IF ( LEFT(Date, 1) = 'Y' , ($(vToday) -1),

IF (Right(Date, 1) = 'm' AND len(Date) > 10 , (TRIM(LEFT(Date,6))&' '& $(vCurrentYear)),

IF (len(Date) < 10 , ($(vToday))

)

as Datum,

Date

FROM

Dates.xlsx

(ooxml, embedded labels, table is Blad1);

Only somehow my Today() variable acts wierd.

In an input box it show the correct date but not in a text field.

See attachment.

Date (time) format challenge

swuehl — Tue, 21 Feb 2012 12:40:02 GMT

I think you should use

=Date('$(vDayToday)')

for your text box. The other way it is first calculating 21-02-2012 (21 minus 2 minus 2012) and then trying to parse that in (you used date#() function) or format (using date() function). The single quotes prevent Qv from doing this.

And for your load, you could try using the mentioned alt() function like that:

Dates:

LOAD *,

timestamp(

alt(

if(time#(Date,'hh:mmTT'),time#(Date,'hh:mmTT')+today(1)),

if(wildmatch(Date,'Yesterday*'),time#(right(Date,7),'hh:mmtt')+today(1)-1),

if(date#(Date,'d MMM hh:mmtt'),date#(Date&year(today(1)),'d MMM hh:mmttYYYY')),

date#(Date,'d MMM YYYY')

)

) as NewDateField;

LOAD * INLINE [

Date, Time format info:

01:35pm, Today

Yesterday 10:25am, Yesterday

18 Feb 2:01pm, Dates this year

3 Jan 11:02am, Dates this year

30 Nov 2011, Dates previous year (and older)

];

Hope this helps,

Stefan

Date (time) format challenge

Anonymous — Tue, 21 Feb 2012 13:21:26 GMT

Nice solution.

Thanks Stefan!