Solved: Get Dates between to Dates from one column - Qlik Community

reporting_neu · ‎2024-08-22

Hello,
I have data that only has a start date. There is no end date.

The date is lined up one below the other in one column.

I need the days between each date.

I have:

Example data:

TMP:
Load * Inline [
PersonID, ShiftID, StartDate
7, 2, '01.01.2023',
7, 5, '01.07.2024',
13, 15, '01.09.2023',
13, 19, '01.12.2023'
];

I need:

Can you help me please?

hic · ‎2024-08-22

When you have the interval, you can create all dates between Start and End using a While-loop. See
https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

hic · ‎2024-08-22

When you have the interval, you can create all dates between Start and End using a While-loop. See
https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

brunobertels · ‎2024-08-22

Hi

try this

TMP:
Load * Inline [
PersonID, ShiftID, StartDate
7, 2, '01.01.2023',
7, 5, '01.07.2024',
13, 15, '01.09.2023',
13, 19, '01.12.2023'
];

temp:
load
date(date#(StartDate,'DD.MM.YYYY'),'DD/MM/YYYY') as DATE,
PersonID, ShiftID
resident TMP;
drop table TMP;

join(temp)
load PersonID,
date(MinDATE + iterno()-1) as DATE
while MinDATE + iterno()-1 <= MaxDATE;

load PersonID,
if(previous(PersonID)=PersonID,today(),date(max(DATE))) as MaxDATE,
date(min(DATE)) as MinDATE
resident temp group by PersonID;

final:
NoConcatenate
load
PersonID,
DATE,
if(isnull(ShiftID),peek(ShiftID),ShiftID) as ShiftID
resident temp
order by PersonID,DATE;
drop table temp;

resulting table sample :

chek table for min max Date by shiftId and Person ID

reporting_neu · ‎2024-08-22

Very easy. Thank you!

Get Dates between to Dates from one column