topic Re: If Logic issue in QlikView

If Logic issue

amit_saini — Tue, 05 Jun 2018 15:07:17 GMT

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

Re: If Logic issue

isingh30 — Tue, 05 Jun 2018 15:11:27 GMT

May be this -

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

Re: If Logic issue

amit_saini — Tue, 05 Jun 2018 15:16:11 GMT

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

Regards,

Re: If Logic issue

olivierrobin — Tue, 05 Jun 2018 15:50:07 GMT

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

Re: If Logic issue

marcus_sommer — Wed, 06 Jun 2018 09:28:25 GMT

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

Re: If Logic issue

sunny_talwar — Wed, 06 Jun 2018 11:40:08 GMT

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

Re: If Logic issue

amit_saini — Wed, 06 Jun 2018 11:56:51 GMT

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

Regards,