Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Date format change in qlik?

Hi community,

     I have date column in my csv file in two formats,

1.format = mm/dd/yyyy

2.number format= 41730,

monthname(Date(alt("Inv Date", Date(Date#("Inv Date", 'MM/DD/YYYY'), 'MMM-YYYY'),

Date(num("Inv Date"), 'MMM-YYYY') ))) as "Invoice Date" ,

used above script to change the format,

when i am trying to change the format to mmm-yyyy, system is getting confused to take some records

for example : 10/01/2015(oct 1st 2015) but it is loading as Jan 2015(Jan 10th 2015).

And am not sure how the number format is changing.

Please help me how can I resolve this.

1 Solution

Accepted Solutions

Re: Date format change in qlik?

This should work for you:

Date(Date#([Inv date], 'MM/DD/YYYY'), 'MMM-YYYY') as [Expected Output]

10 Replies

Re: Date format change in qlik?

May be this:

MonthName(Alt(Date#("Inv Date", 'MM/DD/YYYY'), "Inv Date")) as "Invoice Date" ,

balrajahlawat
Esteemed Contributor

Re: Date format change in qlik?

Try this:

=date(NumberFormatField,'MMM-YYYY') as NumberFormatField

Re: Date format change in qlik?

or even this:

SET DateFormat='MM/DD/YYYY';

MonthName(Date("Inv Date")) as "Invoice Date"

Not applicable

Re: Date format change in qlik?

Hi sunny,

thanks for the reply,

first 10 days of oct are taking as different months,

10/01/2015 as 01/10/2015 (i need Oct 1st)

.

.

.

10/12/2015 as 10/12/2015( I need Oct 12th).

thank you

punitpopli
Contributor III

Re: Date format change in qlik?

Try this :

date(Addmonths(Date#(Date,'Format'),0),'Format')

Not applicable

Re: Date format change in qlik?

Hi Balraj,

Not working,

I have two formats in same column.

10/01/2015

41750

I need to change both to MMM-YYYY format

Thnak you

Not applicable

Re: Date format change in qlik?

Hi Punit,

this is the output am getting when I used the above expression

date.png

Re: Date format change in qlik?

Care to post some sample csv data?

Not applicable

Re: Date format change in qlik?

Hi,

sample data:

Inv dateSmiley SadMM/dd/yyyy) Output am getting   Expected output

10/01/2015                  Jan-2015                Oct-2015

10/02/2015                   Feb-2015                Oct-2015

10/03/2015                   Mar-2015                Oct-2015

10/04/2015                    Apr-2015                Oct-2015

10/05/2015                    May-2015               Oct-2015

10/06/2015                    Jun-2015                Oct-2015

10/07/2015                    Jul-2015                 Oct-2015

10/08/2015                    Aug-2015                Oct-2015

10/09/2015                    Sep-2015                Oct-2015

10/10/2015                    Oct-2015                Oct-2015

10/11/2015                    Nov-2015                Oct-2015

10/12/2015                    Dec-2015                Oct-2015

from 10/13/2015 are storing in Oct month.

thank you

Community Browser