topic Re: Ask Format Date. in QlikView

Ask Format Date.

Fri, 07 Aug 2015 04:05:05 GMT

Dear All,

I create qvw doc that load data from excel file. but some data is different format in one field (format date).

How to equalize the format date from this excel file without edit formate date in the excel file.

hope someone help me.

Regards,

Ted

Re: Ask Format Date.

PrashantSangle — Fri, 07 Aug 2015 04:26:10 GMT

Hi,

Use Alt()

Syntax with description from Help Menu

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters
that has a valid number representation. If no such match is found, the last
parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Will test if the field date contains a date according to any of the three specified date formats.

If so, it will return the original string and a valid number representation of a date.

If no match is found, the text 'No valid date' will be returned (without any valid number representation).

Regards

Re: Ask Format Date.

sunny_talwar — Fri, 07 Aug 2015 04:29:10 GMT

This seems to work:

SET DateFormat='D/M/YYYY';

Table:

LOAD [ENTRANCE DATE],

[EXIT DATE],

company

FROM

ask.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Ask Format Date.

sujeetsingh — Fri, 07 Aug 2015 04:30:57 GMT

You can use two ways to do this.

One is just format it to date and see the different values.

The next way is use conditional conversion based on the deliminter ., that means when it is '-' then one format else the other format.

Re: Ask Format Date.

Fri, 07 Aug 2015 06:25:09 GMT

Dear all,

thank you for your help,

regards,

Ted.