topic Re: Date convert - without Alt function in QlikView

Date convert - without Alt function

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

How to convert the Date column to DATE format.

I tried the below code but format function Date# is not working for second record.

Please advise.

Data:

load * ,DATE(DATE#(Date#(Date,'YYYY/DD/MM'),'YYYY/MM/DD')) AS NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

Re: Date convert - without Alt function

prieper — Wed, 31 May 2017 09:24:42 GMT

What do you want to achieve?

What would be the criteria to differentiate, whether the record is in format YYYY/DD/MM or in format YYYY/MM/DD?

The only chance (but not failproof) would be to identify a Daynumber > 12, but this will not help with entries like 2017/03/04.

Re: Date convert - without Alt function

Anil_Babu_Samineni — Wed, 31 May 2017 09:26:24 GMT

May be this?

Data:

LOAD Sno, Date, Alt(Date(Date#(Date,'YYYY/MM/DD'),'YYYY/MM/DD'), Date(Date#(Date,'YYYY/DD/MM'),'YYYY/MM/DD')) as NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

Re: Date convert - without Alt function

qlikviewwizard — Wed, 31 May 2017 09:29:03 GMT

2010/31/01 and 2011/02/28 are two dates with different formats. I want to correct this data issue without using Alt function to valid date format.

Hope, I explained the issue clearly.

Re: Date convert - without Alt function

qlikviewwizard — Wed, 31 May 2017 09:30:23 GMT

Hi loveisfail‌

I want to check the solution without using Alt function.

Re: Date convert - without Alt function

prieper — Wed, 31 May 2017 09:32:02 GMT

.... and how do you think a differentiation can be made?

Re: Date convert - without Alt function

Anil_Babu_Samineni — Wed, 31 May 2017 09:35:33 GMT

I don't think so Can i know the reason to avoid the Alt() function. We can achieve by help of two inner joins. But, I don't recommend to use that. How we know till how many dates are there. I would always go for Alt() only this case

Re: Date convert - without Alt function

qlikviewwizard — Wed, 31 May 2017 09:38:09 GMT

I agree with you

Re: Date convert - without Alt function

prieper — Wed, 31 May 2017 09:39:17 GMT

You may think about a solution like

MAKEDATE(LEFT(MyDate, 4), IF(MID(MyDate, 6,2) > 12, RIGHT(MyDate, 2), MID(MyDate, 6, 2), IF(MID(MyDate, 6, 2) > 12, MID(MyDate, 6, 2), RIGHT(MyDate, 2)) AS NewDate

However, the question remains, what makes you sure to read entries like 2017/03/04 in the correct way?

Re: Date convert - without Alt function

hemanthaanichet — Wed, 31 May 2017 09:41:46 GMT

Hi Arjun,

I think using alt is the finest way of converting diff date formats into single format

Data:

LOAD Sno, Date, Date(Alt(Date#(Date,'YYYY/MM/DD'), Date#(Date,'YYYY/DD/MM'))) as NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

Re: Date convert - without Alt function

tresB — Wed, 31 May 2017 09:55:03 GMT

You can get the same done using if( IsNum(.. which is actually an expanded version of alt(). Would that be of any help for you?

DATE(If( Isnum(Date#(Date,'YYYY/DD/MM')),Date#(Date,'YYYY/DD/MM'),DATE#(Date,'YYYY/MM/DD'))) AS NEW_DATE;

Re: Date convert - without Alt function

hemanthaanichet — Wed, 31 May 2017 10:27:42 GMT

Brother ,

Isnum() will retun either -1 or 0 whether the above will work?????

Re: Date convert - without Alt function

tresB — Wed, 31 May 2017 10:32:32 GMT

I guess it would not take much of your time - to check if that works or not. Just try yourself like:

Data:

load * ,DATE(If( Isnum(Date#(Date,'YYYY/DD/MM')),Date#(Date,'YYYY/DD/MM'),DATE#(Date,'YYYY/MM/DD'))) AS NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

Did it work? Please let know.

Re: Date convert - without Alt function

hemanthaanichet — Wed, 31 May 2017 10:49:47 GMT

Brother its working if dnt mine can u pls say hw it is possible

Re: Date convert - without Alt function

qlikviewwizard — Thu, 01 Jun 2017 01:50:34 GMT

tresesco‌

You are great. Thanks for the information.

Re: Date convert - without Alt function

Koniki — Thu, 11 Jul 2019 07:39:10 GMT

YOU ONLY TWO FORMATS MENTIONED IF THERE IS DIFFERENT FORMATS

HOW DID YOU WRITE THE EXPRESSIONS