4 Replies Latest reply: Jun 29, 2016 11:27 AM by mrtransformer mrtransformer RSS

    Set values for future months

    mrtransformer mrtransformer

      Hi Guys,

       

      I've a table in below format, in which i've data available till Apr 2016 for each employee.

         

      EmployeeMonthHeadcount
      EdJan-20161
      VincentJan-20161
      SamJan-20161
      EdFeb-20161
      VincentFeb-20161
      SamMar-20161
      EdMar-20161
      VincentMar-20161
      SamMar-20161
      EdApr-20161
      VincentApr-20161
      SamApr-20161

       

      In frond end i want to set all the future months value as the latest months value available in the data. Something like below.

       

          

      JanFebMarAprMayJune JulyAugSepOctNov Dec
      Ed111111111111
      Vincent111111111111
      Sam111111111111

       

      Is it possible to achieve the same using a set analysis expression or is there any other way to achieve this? Please help

      Thanks in advance.

        • Re: Set values for future months
          Michael Granillo

          Apply this process: The Generic Load

           

          This data structure is not recommended for Qlik.  Whatever you're trying to accomplish should probably be done with set analysis. 

          • Re: Set values for future months
            Michael Granillo

            Here's the script:

             

            TestData:

            load * inline [

            Employee, Month, Headcount

            Ed, Jan-2016, 1

            Vincent, Jan-2016, 1

            Sam, Jan-2016 ,1

            Ed, Feb-2016 ,1

            Vincent, Feb-2016, 1

            Sam, Mar-2016 ,1

            Ed ,Mar-2016 ,1

            Vincent, Mar-2016, 1

            Sam, Mar-2016, 1

            Ed, Apr-2016, 1

            Vincent, Apr-2016, 1

            Sam, Apr-2016, 1];

             

             

            trans_data:

               Generic Load Employee,Month,Headcount resident TestData;

              

              

            Set vListOfTables = ;

               For vTableNo = 0 to NoOfTables()

                  Let vTableName = TableName($(vTableNo)) ;

                  If Subfield(vTableName,'.',1)='trans_data' Then

                     Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

                  End If

               Next vTableNo

             

               CombinedGenericTable:

               Load distinct Employee resident TestData;

             

               For each vTableName in $(vListOfTables)

                  Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

                  Drop Table [$(vTableName)];

               Next vTableName

              

               drop table TestData;

            • Re: Set values for future months
              Tim Kendrick

              If your question is "How do I get forecasted data into my table" then I posted a similar answer on this discussion: average sales using set analysis and generate the forecast data?

               

              Here's some pseudo code for creating forecasted data in the data load:

               

              Load all of the data you have available into a table called Data:

              Employee,

              Month,

              Value

               

              Loop MONTH(Month + 1) for however many months you want

                  CONCATENATE new month onto Data table, and specify a formula to calculate the Value for each Employee

              END LOOP

               

              Then, you can use a pivot table or chart to visualize the months as columns or the x-axis, respectively.