How to implement the following Logic in Qlik Sense Data Editor

[omkar_2611]

Hello,

I am new to Qlik Sense. I am trying to implement a logic into qlik sense. I'm not sure how to or where to proceed.

Input Tables:

Account_Details

Job_no	Personal_no	Pay	From	Till
1	101	2000	01.06.2021	30.06.2022

Increase

Increase Percent	From	Till
2.30%	01.01.2022	31.12.2022

Account_Positions

ID	Unique_no	Position
1	91100	Pay
2	91100	Monthly Bonus
3	25000	Rent

In Result Table,

'Pay' is calculated as, "Pay + (Pay * Increase_Percent)" from 'Account_Details' and 'Increase' tables.
e.g. For June 2021, 2000 + (2000 * 2.30%) = 2046

In the period of the 'from' and 'till' of increase pay, same formula is used, but the base pay is selected from last month.
e.g. For January 2022, (From Dec. 2021) 2046 + (2046 * 2.30%) = 2093.06

Output Table:

Job_no	Personal_no	Unique_no	Month_Year	Position	Value
1	101	91100	Jun-21	Pay	2046
1	101	91100	Jul-21	Pay	2046
1	101	91100	Aug-21	Pay	2046
1	101	91100	Sep-21	Pay	2046
1	101	91100	Oct-21	Pay	2046
1	101	91100	Nov-21	Pay	2046
1	101	91100	Dec-21	Pay	2046
1	101	91100	Jan-22	Pay	2093.06
1	101	91100	Feb-22	Pay	2093.06
1	101	91100	Mar-22	Pay	2093.06
1	101	91100	Apr-22	Pay	2093.06
1	101	91100	May-22	Pay	2093.06
1	101	91100	Jun-22	Pay	2093.06

So far, I have loaded the inline table and looped the months. And have created a crosstable table of account details.

Account_Details:
Load *,
Date(AddMonths(From,IterNo()-1)) as months
While Date(AddMonths(From,IterNo()-1)) <= Date(Till);
Load * Inline [
Job_no, Personal_no, Pay, From, Till
01, 101, 2000, 01.06.2021, 30.06.2022
];

Increase:
Load * Inline [
Increase_Percent, From, Till
2.30%, 01.01.2022, 31.12.2022
];

Account_pos:
Load * Inline [
ID, Unique_no, Position
1, 91100, Pay
2, 91100, Monthly Bonus
3, 25000, Rent
];

temp_acc:
CrossTable(Position, Value, 3)
Load
Job_no,
Personal_no,
months,
Pay
Resident Account_Details;

[pedrobergo · Accepted solution]

Hi @omkar_2611 

First question is using Pay records to calculate, that contains same period from Account_detaisl and Increase tables.

You may use IntervalMatch to fill Increase % to all Account_details recs.

Drop Fields From, Till FROM Account_Details; // Dropping fields to avoid synthetic tables

[IntervalMatch]:
Inner Join (Increase)
IntervalMatch ( months )
LOAD From, Till
Resident Increase;

Left Join (Account_Details)
Load months, Increase_Percent
Resident Increase;

Drop Table Increase;

Then you can adapt it to get other payments Positions.

[],

Pedro