Add new record in column

[inam6713]

Hello Qlik Community,

I have a QlikView pivot table with a column named Seperation_Status that contains various records. These records are generated within the script. I need to add Closing Headcount based on the following formula:

Closing Headcount = Opening Headcount + New Joiners – Total Exits + Transfer In/Out

Could you please help me implement this at the script level or provide a solution at the frontend level?

Thank you! 😊

expected output:-

Seperation_Status	Mapping Name for Tool	CT
New Joinee		3
Voluntary		8
Involuntary		-
Retirement		-
Total number of Exits		8
Opening Headcount		337
Transfer In/Out		2
closing_headcount		334

[JandreKillianRIC]

Hi @inam6713 
Can you provide a sample data set? The "trick" here would be to build a Multiplier to change the actual value. 

Like a Exit would be -1 and joiner would be 1. This way you can just do a sum to get the closing headcount. I also assume that Transfer In/Out would already be a negative / positive based on the "end result" - Hence the reason requesting a sample dataset. 

Regards 

[Chanty4u]

Try this 

FinalTable:

LOAD *,

    If(Separation_Status = 'Opening Headcount', CT, 0) AS Opening_Headcount,

    If(Separation_Status = 'New Joinee', CT, 0) AS New_Joiners,

    If(Separation_Status = 'Voluntary' OR Separation_Status = 'Involuntary' OR Separation_Status = 'Retirement', CT, 0) AS Total_Exits,

    If(Separation_Status = 'Transfer In/Out', CT, 0) AS Transfers

RESIDENT SeparationData;

DROP TABLE SeparationData;

ClosingHeadcount:

LOAD 

    'closing_headcount' AS Separation_Status,

    (Sum(Opening_Headcount) + Sum(New_Joiners) - Sum(Total_Exits) + Sum(Transfers)) AS CT

RESIDENT FinalTable;

Concatenate(FinalTable)

LOAD * RESIDENT ClosingHeadcount;

DROP TABLE ClosingHeadcount;