topic Re: Date Load in QlikView

Date Load

Vinothishere — Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I have the following data

Id StartDate EndDate

100 5/1/2014 19:20:00 5/1/2014 23:59:59

101 6/1/2014 10:20:00 8/1/2014 19:30:00

Since Id:101 having the different start date and end date,I need to load it in the following manner:

Id StartDate EndDate

100 5/1/2014 19:20:00 5/1/2014 23:59:59

101 6/1/2014 10:20:00 6/1/2014 23:59:59

101 7/1/2014 00:00:00 7/1/2014 23:59:59

101 8/1/2014 00:00:00 8/1/2014 19:30:00

Thanks in Advance.

Re: Date Load

CELAMBARASAN — Fri, 08 May 2015 06:05:54 GMT

Try this

LOAD
  Id,
  Timestamp(StartDate + 1 -1) < EndDate as it,
  If(IterNo()>1, DayStart(Timestamp(StartDate+Iterno()-1)), StartDate) as StartDate,
  If(DayStart(StartDate + IterNo() -1) = DayStart(EndDate), EndDate, DayEnd(Timestamp(EndDate+Iterno()-1))) as EndDate 
While DayStart(StartDate + IterNo() -1) < EndDate;
LOAD Id, TimeStamp(Timestamp#(StartDate,'D/M/YYYY H:mm:ss')) AS StartDate,TimeStamp(Timestamp#(EndDate,'D/M/YYYY H:mm:ss')) AS EndDate INLINE [
    Id ,StartDate ,EndDate
    100 ,5/1/2014 19:20:00 ,5/1/2014 23:59:59
    101 ,6/1/2014 10:20:00 ,8/1/2014 19:30:00
];

Re: Date Load

anbu1984 — Fri, 08 May 2015 07:22:03 GMT

Load Id,If(IterNo() = 1,StartDate,Timestamp(StrtDt+IterNo()-1,'D/M/YYYY h:mm:ss')) As StartDate,
If(StrtDt + IterNo()-1 = EndDt,EndDate,Timestamp(StrtDt+IterNo()-1+Time#('23:59:59','hh:mm:ss'),'D/M/YYYY h:mm:ss')) As EndDate
While StrtDt + IterNo()-1 <= EndDt;
Load *,Date(Floor(StartDate)) As StrtDt,Date(Floor(EndDate)) As EndDt;
Load Id,Timestamp#(StartDate,'DD/MM/YYYY hh:mm:ss') As StartDate,Timestamp#(EndDate,'DD/MM/YYYY hh:mm:ss') As EndDate Inline [
Id,StartDate,EndDate
100,5/1/2014 19:20:00,5/1/2014 23:59:59
101,6/1/2014 10:20:00,8/1/2014 19:30:00 ];

Re: Date Load

Vinothishere — Mon, 11 May 2015 13:59:42 GMT

can you please explain how it works?

Re: Date Load

anbu1984 — Tue, 12 May 2015 05:22:00 GMT

While StrtDt + IterNo()-1 <= EndDt; -- Iterate for number of days between start and end date

If(IterNo() = 1,StartDate,Timestamp(StrtDt+IterNo()-1,'D/M/YYYY h:mm:ss')) AsStartDate -- In first iteration assign StartDate from your input else keep adding one day to StartDate

If(StrtDt + IterNo()-1 = EndDt,EndDate,Timestamp(StrtDt+IterNo()-1+Time#('23:59:59','hh:mm:ss'),'D/M/YYYY h:mm:ss')) As EndDate -- If StartDate + IterNo is equal to EndDate, then assign EndDate from your input else keep adding one day to your StartDate and keep time as 23:59:59

Re: Date Load

Vinothishere — Tue, 12 May 2015 17:07:10 GMT

thank you very much