Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Ask Format Date.

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.

DATE.png

hope someone help me.

Regards,

Ted

Tags (1)
1 Solution

Accepted Solutions

Re: Ask Format Date.

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);

4 Replies

Re: Ask Format Date.

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

Regards,
Prashant Sangle

Re: Ask Format Date.

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);

sujeetsingh
Honored Contributor III

Re: Ask Format Date.

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.

Not applicable

Re: Ask Format Date.

Dear all,

thank you for your help,

regards,

Ted.

Community Browser