topic Date from/to count units in specific date in QlikView

Date from/to count units in specific date

slvedva123 — Fri, 13 Nov 2020 19:47:20 GMT

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.

Re: Date from/to count units in specific date

edwin — Fri, 13 Nov 2020 20:58:37 GMT

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;

Re: Date from/to count units in specific date

QFabian — Fri, 13 Nov 2020 21:45:18 GMT

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));

drop table Data;

exit script;

Re: Date from/to count units in specific date

slvedva123 — Sat, 14 Nov 2020 19:01:55 GMT

Solution work perfect, thanks!

Re: Date from/to count units in specific date

slvedva123 — Sat, 14 Nov 2020 19:02:15 GMT

Solution work fine, thanks!