4 Replies Latest reply: May 12, 2013 5:54 PM by Henric Cronström RSS

    How to convert Months as dynamic variable in script

      Hello,

       

      I have Excel data set which I want to convert in Qlikview. I am open to suggestion but this is what I am thinking.

       

      Data:

      CountryCityJan11Feb11…..Jan12Feb12……Jan13Feb13
      USNew York10020
      420317
      37137
      JapnTokyo200320
      440240
      0200
      UKLondon103240
      383383
      383486
      IndiaMumbai140280
      1400
      0140

       

      Test:

      CrossTable(Months, population,28 )

      LOAD Country, 

                City,

                concat(Date(AddMonths(Date(Today()),-2),'MMMM') &  'Year(Date(Today(), 'YYYYMMDD')-2)' ), /**JAN 11**/

      ...wil repeat the formula for every month.

       

      but I know its not smartest way to achieve it.

       

      Above report is Monthly. So every month will be added, and old month will be deleted. (e.g. when June 2013 data is available, May 2011 will be deleted)

       

      can anyone help, what should I do? Some example will help. 

        • Re: How to convert Months as dynamic variable in script
          Eddy Sanchez

          First I should create a table with the fieldnames:

           

          SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

           

          FieldNames:

          LOAD 25-RowNo() as ID,Month(AddMonths(Today(),-RowNo()+1))&Right(AddMonths(Today(),-RowNo()+1),2) as FieldName,

          Num(Month(AddMonths(Today(),-RowNo()+1))) as Month,

          Year(AddMonths(Today(),-RowNo()+1)) as Year

          AutoGenerate 24;

           

          Then I should use this table to load your excel dinamically

            • Re: How to convert Months as dynamic variable in script

              Eddy Sanchez wrote:

               

              Then I should use this table to load your excel dinamically

               

              Can you elobrate on that ? So I build the table. How do I load the data into it.

               

              Will it be something like:

               

              FieldNames:

              Load         Country

                               City

                               ? ? ?    

                              ? ? ?

              FROM

              [Trend 2013.xlsx]

              (ooxml, embedded labels, table is [Population Trend]);

               

              I am not able to connect dots here. I am still newbee and learning my ways around Qlikview.

            • Re: How to convert Months as dynamic variable in script
              Srikanth Nalubolu

              I am not sure if I understood your problem exactly, but here is what i suggest from what i understand..

               

              first, make sure how many months data you need to see in the chart at any given time...

               

              if for example any time u need too see data for recent 6 months, you can probably use this

               

              example latest date is 20130510

               

              LET vMonth1= month(Date(MAX(Datefield)))&'-'& year(max(Datefield)) , this gives you May-2013

              LET vMonth2=addmonths(Date(Max(Datefield))-1)&'-'year(admonths(max(Datefield),-1)), this gives you April-2013

              .

              .

              .

              LET vMonth6=addmonths(Date(Max(Datefield))-6)&'-'year(admonths(max(Datefield),-6)), this gives you Dec-2012

               

              so when new date is in next month that is 20130610, still the $(vMonth1) gives you Jun-2013 and $(vMonth6) gives Jan-2013

               

              hope this helps....

              • Re: How to convert Months as dynamic variable in script
                Henric Cronström

                Not sure I understand what the problem is, but I would load the data this way:

                 

                   TempData:

                   CrossTable(Months, population,2)

                   LOAD * From File.txt;

                 

                   Data:

                   LOAD 

                             Country,

                             City,

                             Date#(Months,'MMMYY') as Month /* interpret the Month field */

                             resident TempData;

                 

                   Drop Table TempData;

                 

                Then you will have loaded the months with correct interpretation. If you want to number the months relative today's date, you can add a master calendar that does this:

                 

                   Calendar:

                   Load distinct

                             Month,

                             12*Year(Month) + Month(Month) - 12*Year(Today()) - Month(Today()) as RelativeMonth

                             resident Data;

                   

                HIC