Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Differents Date and Time format in the same field

I am still a new user of QV and here is another issue I am dealing with since 5 days

I have 2 differents date and time format in the same field and I would to convert it into an unique one

There is a format with AM and PM, this one the month it is in one number format (example May = 5 and not 05) this one got secondes (ss) and another format, there is no seconds

I have these formats:

M/DD/YYYY hh:mm:ss

MM/DD/YYYY hh:mm

And here is the output format I would like to get DD/MM/YYYY hh:mm:ss

I should get at the end 860 lines....

Could you help me please

Thank you in advance

 

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Differents Date and Time format in the same field

I finally found a solution myself, here it is

 

ALT(TIMESTAMP(TIMESTAMP#(TimeStamp, 'M/DD/YYYY h:mm:ss TT'), 'DD/MM/YYYY hh:mm:ss'), DATE(DATE#(TimeStamp, 'MM/DD/YYYY hh:mm'), 'DD/MM/YYYY hh:mm:ss')) AS %DATE,

 

Thanks

View solution in original post

3 Replies
Highlighted
Partner
Partner

Re: Differents Date and Time format in the same field

Just use timestamp() function and and that should do the magic. 

LOAD Uuid,
timestamp(TimeStamp) as TimeStamp_New,
TimeStamp
FROM
[Classeur1.xlsx]
(ooxml, embedded labels, table is Feuil1);

Highlighted
Partner
Partner

Re: Differents Date and Time format in the same field

I have tried this before

TIMESTAMP(TIMESTAMP#(TimeStamp, 'M/DD/YYYY h:mm:ss')  AS NEW_DATE,

It doesn't work for all the lines, just those in this format not the others

And when I use what you suggest, the field is empty, it doesn't show up

Any other suggestion?

Thank you, best regards

Highlighted
Partner
Partner

Re: Differents Date and Time format in the same field

I finally found a solution myself, here it is

 

ALT(TIMESTAMP(TIMESTAMP#(TimeStamp, 'M/DD/YYYY h:mm:ss TT'), 'DD/MM/YYYY hh:mm:ss'), DATE(DATE#(TimeStamp, 'MM/DD/YYYY hh:mm'), 'DD/MM/YYYY hh:mm:ss')) AS %DATE,

 

Thanks

View solution in original post