Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert the integer to date format(MMM-YY)

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

 

21 Replies
Not applicable
Author

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 <

MayilVahanan

Hi

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Anonymous
Not applicable
Author

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

lawrenceiow
Creator II
Creator II

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

Anonymous
Not applicable
Author

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!!

sdmech81
Specialist
Specialist

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

Anonymous
Not applicable
Author

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

sdmech81
Specialist
Specialist

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

PrashantSangle

Hi,

create new threads so that other people can help you.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable
Author

Thanks..using string functions made it work!!