9 Replies Latest reply: May 28, 2014 8:02 AM by Priyanka R RSS

    adding extra month's in Qlikview

    Aissam Boumejjane

      I have an Excel sheet with two month. However I want to add all the month's of the year and link them to quarters. (there are no Quaters in the source file)

      So i've created an Inline in Qlikview to load extra month's and quarters. However when I reload and open the Month field, I see that jun and jul are double (one from the source and one from the calander table (that I created) in Qlikview. Futhermore the data is not linked. So if I click on jun I should see the Q3. However this is not the case. Plus Qlikview now sees "month" as a keyfield. That's not something I want.

       

      How can I solve the above? Should I join the field "month" from CIS table with the one I created (the calander table)? If so, how can I do this?

       

      Thanks for you help!!

        • adding extra month's in Qlikview

          Hi,

           

          try to solve it with an general calender.

           

          LET varMin = Num('01/01/2008');

          LET varMax = Num('31/12/2011');

           

          date:

          LOAD date($(varMin) + rowno() - 1) AS tempdate

          AUTOGENERATE $(varMax) - $(varMin) + 1;

           

          calender:

          LOAD

          tempdate AS date,

          DAY(tempdate) AS day,

          WEEK(tempdate) AS week,

          MONTH(tempdate) AS month,

          YEAR(tempdate) AS year,

          'Q'&CEIL(MONTH(tempdate) / 3) AS quarter

          RESIDENT date;

           

          DROP TABLE date;

           

          Then your month-field from the excel sheet should have the same name like in calender.

           

          Regards

          vicky

           

            • Re: adding extra month's in Qlikview
              Aissam Boumejjane

              Hi,

               

               

               

              Unfortunately it doesn’t work. I get the following error message:

               

               

               

              Script line error:

               

              Date:

               

              LOAD date( + rowno() - 1) as tempdate

               

              AUTOGENERATE  -  + 1

               

               

               

              Table not found

               

              Calender:

               

              LOAD

               

              tempdate AS date,

               

              DAY(tempdate) AS day,

               

              WEEK(tempdate) AS week,

               

              MONTH(tempdate) AS month,

               

              YEAR(tempdate) AS year,

               

              'Q'& CEIL(MONTH(tempdate) / 3) AS quarter

               

              RESIDENT Date

               

               

               

               

               

               

              Thanks

               

               

               

              Aissam

                • Re: adding extra month's in Qlikview
                  Or Shoham

                  Regarding the lack of association - one of your month fields appears to be inserting spaces. When using Trim() on the month name, I see the correct association, but when using the Month field as-is, I do not. Try running Trim() on the month name you are deriving and see if that fixes the association.

                   

                  Regarding the key field - if you want to merge your Quarter field into the primary table rather than using a key, simply prefix your inline load with an OUTER JOIN statement (or RIGHT JOIN, which would work equally well in your case).

                   

                  I do agree with vicky, though - it is best to have a master calendar table.

                  • adding extra month's in Qlikview

                    Hi,

                     

                    you have to change the dates into your format. I have not look at this, sry.

                     

                    You have to write:

                     

                    LET varMin = Num('1-1-2010')

                    LET varMax = Num('31-12-2011')

                     

                    Then the generation works.

                     

                    RowNo() is a function that counts the lines in the internal table. But this functions begins with line 1, not with 0.

                    So Qv is able to generate every day between the minimal date and the maximal date. More information to that function you get in the help.

                     

                    Regards

                    vicky