Date (time) format challenge

[Anonymous]

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.

[jagan]

Hi,

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

Regards,

Jagan.

[Anonymous]

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]

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 · Accepted solution]

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]

Nice solution.

Thanks Stefan!