Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dennisnet
Valued Contributor III

Date (time) format challenge

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

DateTime format info:
13:35pmToday
Yesterday 10:25amYesterday
18 Feb 2:01pmDates this year
3 Jan 11:02amDates this year
31 Nov 2011Dates 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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Date (time) format challenge

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

5 Replies
MVP & Luminary
MVP & Luminary

Date (time) format challenge

Hi,

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

Regards,

Jagan.

dennisnet
Valued Contributor III

Date (time) format challenge

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.

dennisnet
Valued Contributor III

Re: Date (time) format challenge

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

                              (Replace

                                        (Replace

                                                  (Replace

                                                            (Replace

                                                                      (Replace

                                                                                (Replace

                                                                                          (Replace

                                                                                                    (Replace

                                                                                                              (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.


MVP
MVP

Date (time) format challenge

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

dennisnet
Valued Contributor III

Date (time) format challenge

Nice solution.

Thanks Stefan!