Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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??
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
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.
Thanks Shiva , let me see if this works.
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.
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
Hi Folks,
I got the solution (Yellow Marked)
Thanks,
AS