topic Re: Add new record in column in App Development

Add new record in column

inam6713 — Mon, 10 Feb 2025 11:05:27 GMT

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

Re: Add new record in column

JandreKillianRIC — Mon, 10 Feb 2025 12:25:16 GMT

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

Re: Add new record in column

Chanty4u — Tue, 11 Feb 2025 16:30:23 GMT

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;