topic Re: Question on Year(Date) in QlikView

Question on Year(Date)

Anonymous — Thu, 20 Nov 2014 04:26:25 GMT

Hi,

I'm trying to use the calendar Master script to create the Year dimension for my data set.

However, i am not sure why some of the year appears as '-' (see Row 7).

Am i doing something wrong? Or do need to first format my date field upon loading?

Row	Year	sum(Value)
Row 1	2009	279.49
Row 2	2010	0.00
Row 3	2011	191,826.79
Row 4	2012	8,593.27
Row 5	2013	101,875.40
Row 6	2014	2,015,958.22
Row 7	-	5,142,751.10

SDate is the date field I am using.

CalendarMaster:

LOAD

Date(SDate) AS SDate,

Year(SDate) AS Year,

'Q' & Ceil(Month(SDate) / 3) AS Quarter,

Month(SDate) As Month,

Day(SDate) As Day,

Week(SDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS SDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(SDate) AS MinDate,

Max(SDate) AS MaxDate

RESIDENT FactSales;

thanks

Derrell

Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 04:36:12 GMT

seems that there are values in your field 'Value' without a Date (or without a proper Date according to the calendar)

Re: Question on Year(Date)

Anonymous — Thu, 20 Nov 2014 04:43:23 GMT

Hi Daniel,

thanks for your reply.

This is some of the dates in the SDate field - looked like a valid date but it is not returning the Year

Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 04:49:52 GMT

I think its because you first have to define SDate as a Date - then you can interpret the Date as a Year. So just move the line:

Year(SDate) as Year

to a preceeding load:

CalendarMaster:

LOAD *,

Year(SDate) as Year

;

Load

...

Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 04:52:08 GMT

in your original load your SDate is interpreted as a Date field,

afterwards in the preceeding load the date field is interpreted to a year

Re: Question on Year(Date)

Anonymous — Thu, 20 Nov 2014 05:09:40 GMT

the strange thing is that it works for some, but some it does not work.

in the preceeding load, i have defined SDate field as date => Date(SDate) as SDate

but it still yield the same result

Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 05:12:59 GMT

no - don't define the date in the preceeding load - you have to move your Year()-line there.

So keep your date() in the normal load (this will be processed first in the scriptrun)

the year() line will be processed based on the date-interpretation from the normal load in a second step by the script run. Please see again the scripting example I gave you

Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 05:15:00 GMT

Sorry Derrell - just realized that you are dealing already with several preceeding loads. So just put the one with the year() on top o the others and it should work fine

Re: Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 05:17:39 GMT

Re: Question on Year(Date)

PrashantSangle — Thu, 20 Nov 2014 05:22:04 GMT

Hi,

Handle this issue in front end

Use

Year(SDate) as calculated Dimension

Regards

Re: Question on Year(Date)

Anonymous — Thu, 20 Nov 2014 05:30:41 GMT

thanks Daniel.

Re: Question on Year(Date)

jonathandienst — Thu, 20 Nov 2014 05:30:44 GMT

I think your master calendar script is OK - I think the problem is that some of your transaction dates have a time component, while the master calendar has only days (integer). This means that the transaction dates do not map into the master calendar and the '-' year value is 'missing'.

Make sure that the transaction dates have no time component - in the load statement that loads the value field, make sure the date is defined something like:

Date(Floor(SDate)) As SDate,

You don't need the Floor in the calendar because you are working with integers already.

HTH

Jonathan

Re: Question on Year(Date)

danieloberbilli — Thu, 20 Nov 2014 05:37:40 GMT

Actually, I agree with the posting of Jonathan Dienst - you should definitely try this out. I just saw that I was wrong regarding interpretation of the date fields (which would be actually done with date#().

Kind Regards from Flinders Street...good to see some people from Melbourne here

Re: Question on Year(Date)

Anonymous — Mon, 24 Nov 2014 23:30:38 GMT

Thanks, Jonathan. Works like a charm!