Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mg_gsi_da
Contributor III
Contributor III

Separate date timeframes into single rows ie. by month

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.

mg_gsi_da_0-1675253334743.png

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

Regards Michael

Labels (1)
2 Solutions

Accepted Solutions
brunobertels
Master
Master

Hi 

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 : 

brunobertels_0-1675272535290.png

 

View solution in original post

mg_gsi_da
Contributor III
Contributor III
Author

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

 

 

 

 

View solution in original post

2 Replies
brunobertels
Master
Master

Hi 

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 : 

brunobertels_0-1675272535290.png

 

mg_gsi_da
Contributor III
Contributor III
Author

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