topic Re: Convert the integer to date format(MMM-YY) in QlikView

Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 06:08:03 GMT

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

Re: Convert the integer to date format(MMM-YY)

Jason_Michaelides — Thu, 13 Sep 2012 06:12:24 GMT

Try:

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

Hope this helps,

Jason

Re: Convert the integer to date format(MMM-YY)

senpradip007 — Thu, 13 Sep 2012 06:45:28 GMT

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

Try this

Hope it helps.

Regards

Pradip

Re: Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 07:01:14 GMT

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

Re: Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 07:03:06 GMT

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

Re: Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 18:43:49 GMT

Hi Guys,

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

requirement

Regards,

Mansoor

Re: Convert the integer to date format(MMM-YY)

MayilVahanan — Thu, 13 Sep 2012 18:51:17 GMT

Can you post a sample data ?

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

Re: Convert the integer to date format(MMM-YY)

MayilVahanan — Thu, 13 Sep 2012 18:54:21 GMT

I think [40176] is your format?

Am i correct?

if so,

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

Hope it helps

Re: Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 19:01:07 GMT

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;

Re: Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 19:05:58 GMT

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

Re: Convert the integer to date format(MMM-YY)

Thu, 13 Sep 2012 19:06:37 GMT

Please find the sample data

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

On Fri, Sep 14, 2012 at 12:24 AM, Mayil Vahanan Ramasamy <

Re: Convert the integer to date format(MMM-YY)

MayilVahanan — Thu, 13 Sep 2012 19:29:50 GMT

Test:

CrossTable(SalesDate, Sales, 3)

LOAD Brand,

Model,

[No of Doors],

40179,

40210,

40238,

40269,

40299

FROM

[..\My Documents\Downloads\SampleData QlikView.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load *,Date(Sales,'MMM-YY') as TestedDate Resident Test;

Edit:

(or)

Load *,Date(Num#(SalesDate),'MMM-YY') as TestedDate Resident Table;

Drop Table Test;

Hope it helps

Re: Convert the integer to date format(MMM-YY)

Anonymous — Wed, 26 Jun 2013 11:47:30 GMT

Hi,

I had a similar issue and with your suggestion I was able to get desired results.

For some reason when CrossTable feature is used the dates are provided in QV as numbers.

Date(Num#(SalesDate),'MMM-YY') is the way to go.

Thx

Re: Convert the integer to date format(MMM-YY)

lawrenceiow — Thu, 08 May 2014 17:22:44 GMT

Excellent, thanks priyan_pa. I had the same problem as user and your answer solved it for me. So, essentially all I needed was Num#(), then I can use the normal QlikView date functions such as Year(), Date(), etc

Re: Convert the integer to date format(MMM-YY)

Anonymous — Tue, 31 Jan 2017 06:09:12 GMT

hi all..I'm facing the same problem..my dates are like [42644.0] for oct 2016, [42614.0] for sept 2016 and so on..I tried everything written above but nothing worked for me..I guess QV is taking it as text..please help!!

Re: Convert the integer to date format(MMM-YY)

sdmech81 — Tue, 31 Jan 2017 06:25:13 GMT

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

Date(SalesDate,'MMM-YY') as Sales_Date

resident Crosstab;

Note: Alter 'MMM-YY' as per need to MM-YY or MM/YYYY

Re: Convert the integer to date format(MMM-YY)

Anonymous — Tue, 31 Jan 2017 08:19:43 GMT

it is not working!!..is there any other way out??

Re: Convert the integer to date format(MMM-YY)

sdmech81 — Tue, 31 Jan 2017 10:00:01 GMT

Hi the problem is with data upon loading.

Qlikview is getting it as string.For that you have to use string modification functions like left, right and remove spl characters.

Aftr that use num on tht along with above expression. If you do not get please send me the excel you r using will do it and give you.

Qlik aftr cross table,I think it is getting date as string so use string modification functions..

Sachin

Re: Convert the integer to date format(MMM-YY)

PrashantSangle — Tue, 31 Jan 2017 10:29:10 GMT

Hi,

create new threads so that other people can help you.

Regards,

Re: Convert the integer to date format(MMM-YY)

Anonymous — Fri, 03 Feb 2017 06:35:22 GMT

Thanks..using string functions made it work!!