Getting the salary of the previous period

[Sarah8]

Bonjour Communauté Qlik,

Je travaille actuellement sur un script Qlik Sense où je charge des données à partir d'un fichier QVD (Payroll.qvd) pour créer une table nommée Payroll_Previous. Dans cette table, j'ai introduit un champ calculé nommé PREVIOUS_PERIOD en fonction de certaines conditions.

Mon objectif est de joindre cette table à une autre table, Payroll en utilisant le Code_Section comme clé commune. De plus, je souhaite récupérer le Salary_Amount de la période précédente (PREVIOUS_PERIOD).

J'ai déja crée un champ PREVIOUS_PERIOD et je veux recupérer le salaire correspondant à cette periode.

Voici mon script : 

Payroll:
LOAD
AutoNumberHash128(Payroll.NUDOSS, Payroll.MATRIC,Payroll.NUMBUL, Payroll.PERPAI) as [Payroll Key],
Payroll.NUDOSS as [Payroll_NUDOSS],
Payroll.MATRIC as MATRIC,
Payroll.CODRUB as Section_Code,
Payroll.MONSAL as Salary_Amount,
Payroll.PERPAI as PERIOD
FROM [$(GetLinkQDS)/Payroll.qvd] (qvd)
Where SubField(Payroll.PERPAI, 'MT', -1) >= 202212 and SubField(Payroll.PERPAI, 'MT', -1) <= 202306;

Payroll_tmp2:
NoConcatenate
LOAD
[Payroll Key],
[Payroll_NUDOSS],
MATRIC,
Section_Code,
PERIOD,
Salary_Amount,
If(Mid(PERPAI, 7, 2) = '01',
'MT' & Num(Mid(PERIOD, 3, 4) - 1) & '12',
'MT' & Num(Mid(PERIOD, 3, 4)) & Num(Mid(PERIOD, 7, 2) - 1, '00')
) as PREVIOUS_PERIOD
Resident Payrol;

Payroll_Previous:
NoConcatenate
LOAD
[Payroll Key],
[Payroll_NUDOSS],
MATRIC,
Section_Code as Prev_Section_Code,
Salary_Amount as Prev_Salary,
PREVIOUS_PERIOD
Resident Payroll_tmp2
ORDER BY MATRIC, PREV_PERIOD DESC;

Payroll_Final:
NoConcatenate
LOAD
*
Resident Payroll_tmp1
ORDER BY MATRIC, PERIOD, Section_Code DESC;

LEFT JOIN
LOAD
[Payroll Key],
[Payroll_NUDOSS],
MATRIC,
Prev_Section_Code,
Prev_Salary,
PREVIOUS_PERIOD
Resident Payroll_Previous
ORDER BY MATRIC, PREVIOUS_PERIOD, Prev_Section_Code DESC;

DROP TABLE Payroll, Payroll_tmp2, Payroll_Previous;

STORE Payroll_Final INTO [$(StoreLinkETL)/Payroll_Final.qvd] (qvd);

Please provide your solutions ASAP !

Thank you 

[F_B]

Hi @Sarah8 ,

here are some corrections that should help.

#Payroll_tmp2
NoConcatenate
LOAD
[Payroll Key],
[Payroll_NUDOSS],
MATRIC,
Section_Code,
PERIOD,
Salary_Amount,

If(Mid(PERPAI, 7, 2) = '01', 

//Ensure proper referencing of PERIOD instead of PERPAI for consistency
'MT' & Num(Mid(PERIOD, 3, 4) - 1) & '12',
'MT' & Num(Mid(PERIOD, 3, 4)) & Num(Mid(PERIOD, 7, 2) - 1, '00')
) as PREVIOUS_PERIOD

Resident Payrol;

//Make sure the source table is named Payroll, not Payrol