Convert the integer to date format(MMM-YY)

[#1]

Hi All,

I have a scenario where i read data from the excel spreadsheet which has a date column of format(MMM-YY), but it is interpreting the data present in that column as an integer as [40179]  for JAN-10.  In This case how do i proceed as to get the desired output in the MMM-YY format.

Regards,

surman

[Jason_Michaelides]

Try:

Date(YourDateField,'MMM-YY'). AS Date

Hope this helps,

Jason

[senpradip007]

Hi

Date(DateTest,'MMM-YY') as DateTest

Try this

Hope it helps.

Regards

Pradip

[#4]

I think you should use DATE#() to read the data from excel and use DATE() to display the it.

DATE( DATE#(date_excel, 'MMM-YY'),'MMM-YY')

Faisal

[#5]

Hi,

Try to change settings in your main tab of qlikview. Snapshot attached for your reference with changes highlighted. Hope this is what yo want and this might be of some help.

Regards

Mayank

[#6]

Hi Guys,

I tried all your approaches, unfortunately none worked as per my

requirement

Regards,

Mansoor

[MayilVahanan]

Hi

     Can you post a sample data ?

= Date(40179,'MMM-YY') gives Jan-10 for me.

[MayilVahanan]

HI

     I think [40176] is your format?

     Am i correct?

      if so,

     = Date(PurgeChar('[40179]','[]'),'MMM-YY') gives Jan -10

     Hope it helps

[#9]

Hi Mayil,

For ur reference i  have posted the sample data as well. It would be nice if you could send me the code as well,

Please find the below code generated while making use of the crosstab utility

Crosstab:

CrossTable(SalesDate, Sales, 3)

LOAD Brand,

     Model,

     [No of Doors],

     [40179],

     [40210],

     [40238],

     [40269],

     [40299]

FROM

(ooxml, embedded labels, table is [Input Data]);

load

SalesDate,

date(date#(SalesDate,'MMM-YY'),'MMM-YY') as Sales_Date

resident Crosstab;

[#10]

Hi,

This is the sample data for ur reference

Brand	Model	No of Doors	Jan-10	Feb-10	Mar-10	Apr-10	May-10
Honda	CIVIC	3	0	1	2	1	1
Honda	CR-Z	3	4	4	2	5	5
Honda	CIVIC	4	5	5	5	8	2
Honda	ACCORD	4	3	3	4	4	3
Hyundai	I10	3	0	1	2	3	4
Hyundai	I20	3	6	9	13	5	6
Hyundai	I40	4	3	4	9	6	9
Hyundai	SONATA	4	4	5	8	9	12
Suzuki	Alto	4	18	16	14	18	12
Suzuki	Swift	4	9	13	11	18	15