topic Re: How Separate Dates? in QlikView

How Separate Dates?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

below is sample data:

Id Startdate Enddate

1 2-jan-2017 00:00:00:00 4 -jan -2017 23:59:59:59

expected Output:

Id Date Hours

1 2-1-2017 24

1 3-1-2017 24

1 4-1-2017 24

Thanks & Regards,

Nagesh

Chanty4u — Tue, 14 Feb 2017 06:08:13 GMT

try like below with your fields

Anil_Babu_Samineni — Tue, 14 Feb 2017 06:33:18 GMT

Does you need Script level?

If so, May be do this using Master calendar to generate missing dates

Directory1:

load * Inline [

Id ,Startdate, Enddate

1 ,2-jan-2017 00:00:00:00 ,4-jan-2017 23:59:59:59

];

Directory2:

load

Id,

Min(Date(Date#(Startdate,'DD-MMM-YYYY hh:mm:ss:ff'),'DD-MM-YYYY')) AS MinDate,

Max(Date(Date#(Enddate,'DD-MMM-YYYY hh:mm:ss:ff'),'DD-MM-YYYY')) AS MaxDate

Resident Directory1

GROUP BY Id;

Directory3:

LOAD

RowNo() as SNo,

Id,

IF(Id<>Previous(Id),(MinDate),DATE((Peek(Date,-1)+1),'DD-MM-YYYY')) AS Date

Resident Directory2

while MinDate+IterNo()-1<=MaxDate ;

drop Table Directory1,Directory2;

And Front end it seems this?