topic Re: Using dimension created for grouping in QlikView

Using dimension created for grouping

Fri, 11 Jul 2014 08:25:55 GMT

I've been having this confusing error for quite some time now. I'm a returning QlikView user and have even went to the extend of forgetting something so simple. So, here's the question:

In script editor I've load a date column (YYYYMM) in and attempting to group them base on different years and month.

(Extracted)

Year(Date#(LastDate,'YYYYMM')) AS endDate,

Month(Date#(LastDate,'YYYYMM')) AS endMonth,

IF(endDate < 2014, 'Past',

IF(endDate = 2014 and endMonth <= 6 ,'2014start',

IF(endDate = 2014 and endMonth <= 12, '2014end',

IF(endDate = 2015 and endMonth <=6, '2015start','2015end')))) AS Category

I ran this script and hit into an error stating that the field 'endDate' wasn't found. Is there any possible solution to this ?

Thanks in advance.

Re: Using dimension created for grouping

alexandros17 — Fri, 11 Jul 2014 08:32:11 GMT

unfortunately no, you have to write again the condition so:

Year(Date#(LastDate,'YYYYMM')) AS endDate,

Month(Date#(LastDate,'YYYYMM')) AS endMonth,

IF(Year(Date#(LastDate,'YYYYMM'))< 2014, 'Past',

IF(Year(Date#(LastDate,'YYYYMM'))= 2014 and Month(Date#(LastDate,'YYYYMM'))<= 6 ,'2014start',

IF(Year(Date#(LastDate,'YYYYMM'))= 2014 and Month(Date#(LastDate,'YYYYMM'))<= 12, '2014end',

IF(Year(Date#(LastDate,'YYYYMM'))= 2015 and Month(Date#(LastDate,'YYYYMM'))<=6, '2015start','2015end')))) AS Category

But you can do even a second Load:

LOAD *,

IF(endDate < 2014, 'Past',

IF(endDate = 2014 and endMonth <= 6 ,'2014start',

IF(endDate = 2014 and endMonth <= 12, '2014end',

IF(endDate = 2015 and endMonth <=6, '2015start','2015end')))) AS Category;

...

Year(Date#(LastDate,'YYYYMM')) AS endDate,

Month(Date#(LastDate,'YYYYMM')) AS endMonth,

...

Let me know

Re: Using dimension created for grouping

Fri, 11 Jul 2014 08:41:54 GMT

Hey Alessandro,

The first method will definitely works without a hitch, but I'm looking for a smarter way around it.

So I tried second loading, but it didn't work, same error occurs even though I've already stated the field in the first loading statement.

Re: Using dimension created for grouping

alexandros17 — Fri, 11 Jul 2014 08:47:35 GMT

Remember the ";" at the end of the first load:

Load

.....;

Select (or Load)

....

;