topic Re: Alt function question in QlikView

Alt function question

Thu, 06 Nov 2014 10:35:32 GMT

Hi All,

I have a question I was hoping someone would be able to help me with. I am trying to write a command to fetch the date format from a column filled with dates: some are ddmmyyyy, some are mmddyyyy and so on and so forth. I was advised to use the ALT function, which now takes the following shape:

ALT( date#([Date], 'DD/MM/YYYY'), date#([Date], 'MM/DD/YYYY'), date#([Date], 'DD/MM/YYYY'), 'No Date Found') AS [Date Type]

But the function populates my 'Date Type' column with 'No Date Found' and doesn't recognise any of the dates in the 'Date' String. I tried running a trial command using ALT(date#([Date], ' correct date format specified in the initial set command'), 'No Date Found'), and the function still returns 'No Date Found'.

Any ideas on how to fix that?

Re: Alt function question

CELAMBARASAN — Thu, 06 Nov 2014 10:41:10 GMT

Did you tried Date function around the Date# ?

ALT( Date(date#([Date], 'DD/MM/YYYY')), Date(date#([Date], 'MM/DD/YYYY')), Date(date#([Date], 'DD/MM/YYYY')), 'No Date Found')

Re: Alt function question

anbu1984 — Thu, 06 Nov 2014 10:42:31 GMT

You said your input is of format ddmmyyyy, mmddyyyy without '/'. But you have used '/' in Alt.

Re: Alt function question

Colin-Albert — Thu, 06 Nov 2014 10:42:33 GMT

With your dates being DDMMYYYY or MMDDYYYY how can you tell if 01022014 refers to the 1st February 2014 or 2nd January 2014 ?

Re: Alt function question

tresB — Thu, 06 Nov 2014 10:44:33 GMT

Date#() is not being able to parse it because of wrong format. Try(without '/' symbol), like:

ALT( date#([Date], 'DDMMYYYY'), date#([Date], 'MMDDYYYY'), date#([Date], 'YYYYDDMM'), 'No Date Found') AS [Date Type]

Re: Alt function question

MK_QSL — Thu, 06 Nov 2014 10:46:17 GMT

If you have two different formats like

DD/MM/YYYY and MM/DD/YYYY

There is no possibility to recognize them..

12/03/2014 and 12/03/2014.... Both could be either 3rd December or 12th March

ALT function can be used for more than one date formats something like

DD/MM/YYYY and DD.MM.YYYY and DD-MM-YYYY

DD/MM/YYYY and MM.DD.YYYY and YYY-MM-DD

Hope this will make clear...

Re: Alt function question

Thu, 06 Nov 2014 10:53:15 GMT

I used yyyyddmm and so forth as shorthand, the default format specified uses '/'s.

Re: Alt function question

Thu, 06 Nov 2014 10:54:29 GMT

Thanks for your response. I tried 'DDMMYYYY' and so forth, as well as 'DD-MM-YYYY', to no avail.

Re: Alt function question

Thu, 06 Nov 2014 10:56:30 GMT

I see- that actually makes perfect sense. I import my data from an excel spreadsheet and I was hoping that maybe it checks out the excel formatting while importing the data strings. Do you think there is any way to check the actual data format (DDMMYYYY vs MMDDYYYY and so forth)?

Re: Alt function question

Colin-Albert — Thu, 06 Nov 2014 11:00:30 GMT

With some dates the difference is obvious - 31122014 can only be DDMMYYYY, but for dates in the first 12 days of the month, you cannot tell id a date is DDMMYYYY or MMDDYYYY as both options return valid dates.

02122014 can be 2nd December or 12th February.

You will need to include some other indicator to show what date format is being used unless you can extract all dates in a common format.

Re: Alt function question

MarcoWedel — Thu, 06 Nov 2014 11:45:30 GMT

please provide sample data/application

Re: Alt function question

maniram23 — Sun, 04 Oct 2015 14:07:18 GMT

Hi,

Try this one. it is help full for you.

date(alt(date#(DateString, 'YYYY-MM-DD'), date#(DateString, 'DD-MM-YYYY'), date#(DateString, 'DD/MM/YYYY'), 'NO DATA FOUND'),'DD/MM/YYYY') AS Date