topic Re: Separate date timeframes into single rows ie. by month in App Development

Separate date timeframes into single rows ie. by month

mg_gsi_da — Wed, 01 Feb 2023 12:15:13 GMT

Hi Experts,

we use a database to define the salary of our employees for specific timeframes. Qlik should be used to get an outlook for salary cost for a specific month in the future. Maybe using a pivot table.

Unfortunately the salary is not available as a table with one row per month per employee. There are only timeframes listed.

So I need to split a timeframe into single rows per month and employee. Most likely using "autogenerate" but don't know how to do that for more than one employee. See example below.

Person A receives a salary increase starting 01.01.2024. Person B is only valid for 4 months.

So the salary cost forecast for 06/2023 would be 2000+3000=5000.

How do I need to code the load script in Qlik Sense to do that?

Regards Michael

Re: Separate date timeframes into single rows ie. by month

brunobertels — Wed, 01 Feb 2023 17:29:07 GMT

try this

[Table]:

load *,
date(AddMonths(valid_from,iterno()-1),'MM/YYYY') as MonthYear
while date(valid_to)>=AddMonths(valid_from,iterno()-1) ;
;

LOAD * INLINE
[
person,valid_from,valid_to,salary
A,01/02/2023,31/12/2023,2000
B,01/03/2023,30/06/2023,3000

A,01/01/2024,28/02/2024,2200
A,01/01/2024,28/02/2024,2200
](delimiter is ',');

Table :

Re: Separate date timeframes into single rows ie. by month

mg_gsi_da — Thu, 02 Feb 2023 06:48:31 GMT

Hi Bruno,

if I copy your code to the script it just returns an empty table with the columns of the Inline Load. No data is loaded.

BUT: The reason was that we use the german date format dd.mm.yyyy. After I replaced the / with . it worked well

Thx a lot