Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

5 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.


swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

Nice solution.

Thanks Stefan!