Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Just wondering if there is any script that will help me distinguish European date format ddmmyyyy from US date format mmddyyyy? I have an excel document with a date column that contains the two formats(the column itself is formatted as general). Instead of changing excel, is there qlikview script that would allow me to create a new field containing a single format? Thanks.
If you load the Date using
Date(
Alt(
Date#(Date,'DDMMYYYY'),
Date#(Date,'MMDDYYYY')
)
) as Date
Then the Alt() function will return the first valid interpretation. (You can also have a 3rd and 4th parameter, etc.)
If you just want to test whether an interpretation is valid, you can use
IsNum(Date#(Date,'DDMMYYYY'))
If you load the Date using
Date(
Alt(
Date#(Date,'DDMMYYYY'),
Date#(Date,'MMDDYYYY')
)
) as Date
Then the Alt() function will return the first valid interpretation. (You can also have a 3rd and 4th parameter, etc.)
If you just want to test whether an interpretation is valid, you can use
IsNum(Date#(Date,'DDMMYYYY'))