Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Solution work fine, thanks!
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;
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;
Solution work perfect, thanks!
Solution work fine, thanks!