Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
Remember the ";" at the end of the first load:
Load
.....;
Select (or Load)
....
;