topic Re: DATE FIELD IN CROSS TABLE in QlikView

DATE FIELD IN CROSS TABLE

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: DATE FIELD IN CROSS TABLE

nevilledhamsiri — Sun, 13 Oct 2019 16:23:21 GMT

Dear Members,

Look forward to your response

Thanks

Re: DATE FIELD IN CROSS TABLE

rwunderlich — Sun, 13 Oct 2019 18:21:29 GMT

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

Re: DATE FIELD IN CROSS TABLE

nevilledhamsiri — Mon, 14 Oct 2019 00:26:17 GMT

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

Re: DATE FIELD IN CROSS TABLE

rwunderlich — Mon, 14 Oct 2019 15:32:54 GMT

Please post your xlsx file and your latest qvw.

-Rob

Re: DATE FIELD IN CROSS TABLE

Victor_Alumanah — Mon, 14 Oct 2019 16:01:19 GMT

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

Re: DATE FIELD IN CROSS TABLE

nevilledhamsiri — Mon, 14 Oct 2019 17:00:20 GMT

Dear Rwunderlich

This is my Xls & qvd

Please look in to this

Thanks

Neville

Re: DATE FIELD IN CROSS TABLE

rwunderlich — Mon, 14 Oct 2019 17:13:00 GMT

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

Re: DATE FIELD IN CROSS TABLE

nevilledhamsiri — Mon, 14 Oct 2019 17:13:46 GMT

Hi Tilnu,

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

Neville

Re: DATE FIELD IN CROSS TABLE

nevilledhamsiri — Mon, 14 Oct 2019 17:19:41 GMT

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

Re: DATE FIELD IN CROSS TABLE

rwunderlich — Mon, 14 Oct 2019 18:26:10 GMT

I don't know why numbers get converted to strings in a CrossTable. It's something I've been aware of for a long time, but don't know the reason. Anyone know?

-Rob

Re: DATE FIELD IN CROSS TABLE

nevilledhamsiri — Wed, 16 Oct 2019 17:37:43 GMT

Thanks a lot

Neville