3 Replies Latest reply: Jul 11, 2014 4:47 AM by Alessandro Saccone RSS

    Using dimension created for grouping

      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
          Alessandro Saccone

          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