Date from/to count units in specific date

[slvedva123]

Hello,

Does someone can help with qlikview script, I have data table like:

Unit; StartDate; Enddate

A123; 2020.10.01;2020.10.04

B123; 2020.10.03;2020.10.08

C123; 2020.10.04;2020.10.10

A123; 2020.10.6; 2020.10.12

Result I want to get:

Date and count of units:

2020.10.01   1

2020.10.02   1

2020.10.03   2

2020.10.04   3

2020.10.05   2

2020.10.06   3 etc.

[edwin · Accepted solution]

I would solve this problem on the script side by building a bridge.  the bridge associates the start and enddate range to the specific calendar dates.  so in you expression you only need the date as dimension and count(Unit) as expression:

SET DateFormat='YYYY-MM-DD';

data:
load *, StartDate & '|' & Enddate as %bridgekey inline [
Unit, StartDate, Enddate
A123, 2020-10-01,2020-10-04
B123, 2020-10-03,2020-10-08
C123, 2020-10-04,2020-10-10
A123, 2020-10-06, 2020-10-12
];

//this is just to generate dates from 10/1 to 10/12
calendar:
load date(Date('2020-10-01')+rowno()-1) as Date;
load 1 as rnum AutoGenerate(12);

NoConcatenate
tmpBridge: load distinct StartDate, Enddate, %bridgekey Resident data;
inner join (tmpBridge) load Date Resident calendar;

NoConcatenate
Bridge: load %bridgekey, Date Resident tmpBridge where Date >= StartDate and Date <=Enddate;

drop table tmpBridge;

[QFabian]

Hi!, @slvedva123 , try this :

Data2:
LOAD * INLINE [
Unit, StartDate, Enddate
A123, 2020.10.01, 2020.10.04
B123, 2020.10.03, 2020.10.08
C123, 2020.10.04, 2020.10.10
A123, 2020.10.06, 2020.10.12
];

Data:
Load
Unit,
Enddate,
StartDate,
(Enddate - StartDate)+1 as Days
Resident Data2;
drop table Data2;

Let vRows = NoOfRows('Data')-1;

For vRow = 0 to $(vRows)

Let vUnit = peek('Unit', $(vRow), 'Data');
Let vDays = peek('Days', $(vRow), 'Data');
Let vStartDate = peek('StartDate', $(vRow), 'Data');
Let vEnddate = peek('Enddate', $(vRow), 'Data');
Let vDate = peek('StartDate', $(vRow), 'Data') + $(vRow);

Dates:
Load
'$(vUnit)' as Unit,
'$(vStartDate)' as StartDate,
'$(vEnddate)' as Enddate,
$(vDays) as Days,
date(if(rowno()=1, '$(vDate)', if('$(vUnit)' = peek(Unit), (peek(Date)+1), '$(vStartDate)')), 'DD-MM-YYYY') as Date
autogenerate ($(vDays));

Next

drop table Data;

exit script;

[slvedva123]

Solution work perfect, thanks!

[slvedva123 · Accepted solution]

Solution work fine, thanks!