Turning Different Formats into one.

[#1]

Hi there, I've got a little bit of a problem, i'm building a callcentre model and some sites use different Timestamps after typing in times of their calls etc eg. one pharmacy - '2009-12-03 12:05:10.000'

another pharmacy - '2009-03-12 12:05:10.000'

As you can see the Days and Months are different. Now usually to get the correct Date i would use the Date() function, the Left() function etc to get to eg. 200905 for YearMonth or 2009 for Year or 20090528 for FullDate. I also would like the Time in there eg. 12:58pm etc. Please could someone show me how to go about this, the Field name is Timestamp.

All the previous functions do not work because apparently QlikView assumes that the Format is YYYYMMDD, unfortunately my format differs, but i would like to get all the TimeStamps to get to that Format (YYYYMMDD).

Thanks

Rich

[#2]

By the way, it is in the script.

[Miguel_Angel_Baeyens]

Function timestamp#(date,'format') is useful for that. Unfortunately, you have to have any criteria to match your different values into one "format" so timestamp# can work properly, meaning if field Date has no control on the data the user can input (it doesn't matter the month/day order) it is more a problem of data sources than QlikView. Anyway, let us know if timestamp# would work for you and if you can separate one kind of dates from the others.

Regards.

[Miguel_Angel_Baeyens]

QlikView uses three environment variables which are set in the Main tab in the top.

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

[#5]

Ok I tried that, it didn't work... Qlikview still doesn't recognize that the formats are different in the same field, i'm going to have to try and sort it out from the source... Thanks anyway..

[prieper · Accepted solution]

There are two functions: TIMESTAMP#('YourDate', 'Format of your Date') does read the string and interpretes this as a number, exactly in the format given. The other one is TIMESTAMP(YourNumber [,'Format to Display']) - the '[Format to Display]' is optional, if omitted the environment-variable will be used. Back to your problem, think you should use a formula like:

TIMESTAMP(IF(Pharmacy = 'A',
TIMESTAMP#(CallTime, 'YYYY-MM-DD hh:mm:ss.fff'),
TIMESTAMP#(CallTime, 'YYYY-DD-MM hh:mm:ss.fff')),'YYYYMMDD hh:mm')

HTH
Peter

[#7]

Thanks, worked perfectly...