topic Re: Alt function in QlikView

Alt function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I have Date field with different formats:

JAN 1 2018 12:00AM

2018-02-01

2018/03/01

How to convert in one form of date ?

Re: Alt function

Chanty4u — Tue, 19 Jun 2018 05:47:53 GMT

Hi here is one document i have created for this case

Multiple Dates Using Alt

Re: Alt function

Anonymous — Tue, 19 Jun 2018 06:06:38 GMT

Thanks for the document.

But Still for JAN 1 2018 12:00AM , I tried Date(alt(TimeStamp#(FromDate,'MMM DD YYYY hh:mmTT')),'DD-MM-YYYY').

It is not working

Re: Alt function

Gowtham174 — Tue, 19 Jun 2018 06:28:07 GMT

Try this

Date:

load *,

date(alt( date#( date1 , 'YYYY-MM-DD' ),

timestamp#(date1,'MMM D YYYY hh:mmTT'),

date#(date1 , 'YYYY/MM/DD' ),'NO' )) as NewDate;

LOAD * inline [date1

JAN 1 2018 12:00AM

2018-02-01

2018/03/01];

Re: Alt function

Anonymous — Tue, 19 Jun 2018 08:31:21 GMT

Hi,

Code: timestamp#(date1,'MMM D YYYY hh:mmTT') is not working for date format JAN 1 2018 12:00AM

Re: Alt function

Peter_Cammaert — Tue, 19 Jun 2018 11:01:11 GMT

The script posted by Gowtham works perfectly for the three cases that you posted in your question.

The only reason why it wouldn't work for you is maybe because you are using different source data. Can you post an example document with some sample data that isn't getting translated using this Alt() technique?

Peter

Re: Alt function

Anonymous — Wed, 20 Jun 2018 05:18:12 GMT

there was an extra space in timestamp date format . currently code is working fine

Date:

load *,

date(alt( date#( date1 , 'YYYY-MM-DD' ),

timestamp#(date1,'MMM D YYYY hh:mmTT'),

date#(date1 , 'YYYY/MM/DD' ),'NO' )) as NewDate;