Logic help for timings filter

[amit_saini]

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,

AS

[Anonymous]

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??

[amit_saini]

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,

AS

[Anonymous]

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.

[amit_saini]

Thanks Shiva , let me see if this works.

[ajsjoshua]

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.

[amit_saini]

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,

AS

[amit_saini · Accepted solution]

Hi Folks,

I got the solution (Yellow Marked)

Thanks,

AS