Set values for future months

[#1]

Hi Guys,

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

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

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

	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	Dec
Ed	1	1	1	1	1	1	1	1	1	1	1	1
Vincent	1	1	1	1	1	1	1	1	1	1	1	1
Sam	1	1	1	1	1	1	1	1	1	1	1	1

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.

[mgranillo]

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. 

[mgranillo]

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;

[TKendrick20]

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.

[#5]

Thanks Kendrick.

In this case i'd need to generate records for all the employees for remaining months in the year.

Since I've data for more than 1L employees and for different transactions like joiners and leavers etc., it would be a bit difficult to manage that. Is there any way doing it using set analysis.