Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
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.
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.