Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

nevilledhamsiri
Valued Contributor

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
MVP & Luminary
MVP & Luminary

Re: DATE FIELD IN CROSS TABLE

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
Valued Contributor

Re: DATE FIELD IN CROSS TABLE

Dear Members,

Look forward to your response

 

Thanks

 

MVP & Luminary
MVP & Luminary

Re: DATE FIELD IN CROSS TABLE

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
Valued Contributor

Re: DATE FIELD IN CROSS TABLE

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

MVP & Luminary
MVP & Luminary

Re: DATE FIELD IN CROSS TABLE

Please post your xlsx file and your latest qvw.

-Rob

Victor_Alumanah
Contributor

Re: DATE FIELD IN CROSS TABLE

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
Highlighted
nevilledhamsiri
Valued Contributor

Re: DATE FIELD IN CROSS TABLE

Dear Rwunderlich

 

This is my Xls & qvd

Please look in to this

Thanks

Neville

 

MVP & Luminary
MVP & Luminary

Re: DATE FIELD IN CROSS TABLE

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

nevilledhamsiri
Valued Contributor

Re: DATE FIELD IN CROSS TABLE

Hi Tilnu,

 

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

 

Neville

 

 

nevilledhamsiri
Valued Contributor

Re: DATE FIELD IN CROSS TABLE

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