topic Re: Logic help for timings filter in QlikView

Logic help for timings filter

amit_saini — Mon, 03 Apr 2017 05:50:58 GMT

Hi Folks,

Below is my scenario :

and here are "Client" comments on above results:

So I need help for the "Nacht" shift as per client suggestion.

Attached is my qvw.

Thanks,

Re: Logic help for timings filter

Anonymous — Mon, 03 Apr 2017 06:20:53 GMT

If it can be handled in the script. How about doing an if else clause like if (time <6) then date-1 else date as date??

Re: Logic help for timings filter

amit_saini — Mon, 03 Apr 2017 07:37:18 GMT

According to Client the yellow marked entry should reflect for "26 March" Nacht shift and not for 27.

So in general entries between 00:00 and 06:00 AM should be part of previous day Nacht shift and not the current date.

One more example below:

All yellow marked entries are in between 00:00 and 06:00 AM in below example should belongs to 24 March in Nacht shift

Hope this is clear!

Thanks,

Re: Logic help for timings filter

Anonymous — Mon, 03 Apr 2017 09:01:01 GMT

Hi Amit,

While loading the data and creating shift field,

if timestamp(Timestamp('2012-10-16 09:21:01', 'YYYY--MM-DD HH:MM:SS'),'HH') <6 then take shift as Nacht.

Am not sure if am clear.

Re: Logic help for timings filter

amit_saini — Mon, 03 Apr 2017 09:28:17 GMT

Thanks Shiva , let me see if this works.

Re: Logic help for timings filter

ajsjoshua — Mon, 03 Apr 2017 10:14:31 GMT

Hi Amit,

In script

If(Frac(FieldName) >= MakeTime(6) and Frac(FieldName) <= MakeTime(24), 'Day', 'Night') as Flag

In exp

Assign a condition if Flag is night then consider it as previous day.

Regards,

Joshua.

Re: Logic help for timings filter

amit_saini — Mon, 03 Apr 2017 10:37:37 GMT

Hello Joshua,

First I want to explain you few things:

This scrap information is coming from "CAQ" table which is having Date filed (including timestamp) and to divide this data into different shifts we are using a shift table and below scenario to drive different date entries into Shift by using interval match function:

So below I'm sharing the script to achieve this concept of linking , but I'm not sure what we can do or if your suggestion is applicable also for this script:

Shift:

LOAD Shift_Id,

Shift_Name,

num(Start_Hour) as Start,

num(End_Hour - Time#('00:00:01'),'0,0000000') as End,

Line_Id,

Facility_Id

FROM

D:\qvprod\Operation\CBS\#Source\Qvd\Shift.qvd

(qvd);

//exit script;

Shifts:

//Shift_Early_Late:

NoConcatenate

LOAD

Resident Shift

where End > Start;

Shift_Night:

NoConcatenate

LOAD

Resident Shift

where Start > End;

Shift_Night_1:

Concatenate (Shifts)

LOAD

Shift_Id,

Shift_Name,

Start,

1 as End,

Line_Id,

Facility_Id

Resident Shift_Night;

Shift_Night_2:

Concatenate (Shifts)

LOAD

Shift_Id,

Shift_Name,

0 as Start,

End,

Line_Id,

Facility_Id

Resident Shift_Night;

drop tables Shift, Shift_Night;

MAP_MaxDate:

Mapping

LOAD Facility_Id &'|'& Auto as %KeyMaxDate,

num(max(Date)) as Date

FROM

(qvd)

group by Facility_Id, Auto;

CAQ:

LOAD

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

num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without substraction of 0.0000001 the time 21:59:59 was not matched?!

year(Date.tmp) as Year,

month(Date.tmp) as Month,

Day(Date.tmp) as Day,

num(Date.tmp) as NumDate,

dual(year(Date.tmp)&'-'&'Q'&ceil(month(Date.tmp)/3),year(Date.tmp)&ceil(month(Date.tmp)/3)) as Year_Quarter,

dual(year(Date.tmp)&'-'&month(Date.tmp),year(Date.tmp)&num(month(Date.tmp),'00')) as Year_Month,

dual(year(Date.tmp)&'-'&week(Date.tmp),year(Date.tmp)&num(week(Date.tmp),'00')) as Year_Week,

;

LOAD

ApplyMap('MAP_MaxDate',Facility_Id &'|'& Auto,'no match') as Date.tmp,

timestamp(Date) as Date_Org,

ApplyMap('Mapp_Technology',Station_Label) as Technology,

ApplyMap('Mapp_LineSection',Station_Label) as LineSection,

Facility_Id,

Auto,

Line_Name,

Product_Name,

Station_Name,

Station_Label,

Quantity,

Type_Name,

Reason_Name

FROM

(qvd);

left join (CAQ)

IntervalMatch (Time,Facility_Id)

Load

Start,

End,

Facility_Id

Resident Shifts;

left join(CAQ)

LOAD distinct

Facility_Id,

Start,

End,

Shift_Name

Resident Shifts;

drop table Shifts;

Thanks,

Re: Logic help for timings filter

amit_saini — Wed, 05 Apr 2017 07:09:02 GMT

Hi Folks,

I got the solution (Yellow Marked)

Thanks,