If Logic issue

[amit_saini]

Hi Folks,

My main concern is regrading below red marked condition:

CAQ:

LOAD      

     date(floor(Date.tmp)) as Date,

     num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time,

     year(Date.tmp) as Year,

     month(Date.tmp) as Month,

     week(Date.tmp) as Week,

     Day(Date.tmp) as Day,

     *

     ;

LOAD

*,

hour(Date.tmp2) as Time.tmp,

IF(hour(Date.tmp2) < 7, Date.tmp2-1,Date.tmp2) as Date.tmp 

;

LOAD

    num(Stamp_Date) as Date.tmp2,

    timestamp(Stamp_Date) as Date_Org,

     Id,

     Station_Id,

     ScheduledMinutes

FROM

(qvd);

I need to consider each single day from 7AM today to 7 AM next day and using red marked  condition I'm getting below result, but it should also include next day 07:00 AM ,but currently not. See below

Even if I make it "iF(hour(Date.tmp2) <= 7" then it's showing me from 8:00 AM to next day 07:00 AM , but I want from 7 to 7.

Please suggest how this can be possible.

Regards,

Amit

[isingh30]

May be this -

IF(hour(Date.tmp2) < 7, 'Date.tmp2-1','Date.tmp2') as Date.tmp

[amit_saini]

This can't be possible , you cannot call b/w single quotes.

Regards,

AS

[olivierrobin · Accepted solution]

hello

i don't understand very well .

if your day start at 7, it should end not at 7 but at 6:59:59

so you rule base on hour seems ok

all hours beginning at 7 and next hours will be on one day

and all hours less than 7 will be in previous

In all cases, the hour 7 can't be on 2 days

[marcus_sommer]

I think you need to use an offset-approach like used by fiscal years: Fiscal Year‌. Here an example for it:

load

     *, date(floor(Timestamp)) as Date, time(frac(Timestamp)) as Time,

     date(floor(Timestamp - maketime(7))) as DateWithOffset;

load timestamp(43000 + recno() -1 + ((iterno() -1)) / 24) as Timestamp

autogenerate 5 while iterno() <= 24;

- Marcus

[sunny_talwar]

If none of the above responses help, would you be able to share Planned_Downtime_Stations.qvd?

[amit_saini]

Sorry my thinking was wrong, it has to be from 07:00 AM to 06:00 AM next day for 24 Hrs.

Regards,

AS