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: 
nevilledhamsiri
Specialist
Specialist

DATE FIELD IN CROSS TABLE

Hi

I need the date to be renamed as Month in the data loading done in cross table prefix.Can any one see where I have gone wrong .

 

Thanks in advance

 

Neville

 

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

It's an odd side effect of CrossTable that numbers get converted to strings. So you will have to convert back before you make it a date. Add num#() in your script statement like this:

Month(num#(DATE))AS tran_MONTH,

-Rob

View solution in original post

11 Replies
nevilledhamsiri
Specialist
Specialist
Author

Dear Members,

Look forward to your response

 

Thanks

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You date field is named "Date" by the CrossTable so this line does not make sense to me:

Date(Date#('MM DD YYYY'))   as Month,

I believe it should be:

Date(Date)   as Month,

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

nevilledhamsiri
Specialist
Specialist
Author

Thanks Rwunderlch for your reply.

But I am still getting null value for Month. I need to appear Months such as Jan, Feb etc. Initially the date format given is like (mm/dd/yyyy). Can you please let me know how I get this.

 

Neville

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Please post your xlsx file and your latest qvw.

-Rob

Victor_Alumanah
Creator
Creator

SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;

..............
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET DateFormat='MM/DD/YYYY';

1. You have DateFormat in there twice, I wonder if that has anything to do with it

 

While we teach we learn
nevilledhamsiri
Specialist
Specialist
Author

Dear Rwunderlich

 

This is my Xls & qvd

Please look in to this

Thanks

Neville

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

It's an odd side effect of CrossTable that numbers get converted to strings. So you will have to convert back before you make it a date. Add num#() in your script statement like this:

Month(num#(DATE))AS tran_MONTH,

-Rob

nevilledhamsiri
Specialist
Specialist
Author

Hi Tilnu,

 

What you said formats are already there. Please check my xlxs & qvd to help me. 

 

Neville

 

 

nevilledhamsiri
Specialist
Specialist
Author

Dear  Rwunderlich

 

Thanks so much for your great help to overcome the issue. Could you please brief me on the difference of date format from a normal loading & cross table loading. Why the date get converted in a string in a cross table load etc

 

Thanks so much

 

Neville