min and max issue - Plz help urgent

[#1]

Hi All,

I have a requirement where as I need to subtract the first shift (Max value - min value) at a particular cut of point of shift change. My shift chnages twice in a day.. 1. Moring 6AM  2. Evening 18PM (6 PM) - 24 hour clock is used.

Please check the attached excel for more clear picture...

Here there is only one shift change as the work has started from 12 in the afternoon..

Please look at the colored values in Completion time column. also remove the filter if you would like to see the full data under copletion time column

Any help would be greatly appreciated.

Thanks in advance..

[#2]

Lalitha,

may you show od describe what analysis you would like to prepare using your data?

How many "Move Gapes" you would like to calculate?

Do you need to do it during reload, or Ad-hoc, regarding user selections?

Are there some additional criteria of mathing rows to calculate gap (for exaple the same QC or Moves value)?

regards

Darek

[#3]

Hi Dariusz,

i would like to see the the impact of shift change.. lets say if i find the difference of the max value before my shift change (18PM - evening) and the min value of my first move made after my shift change . i would understand is there any delay made by the engineer once the shift is changed.. means if the engineer has come late and all this can be analysed.

Thanks

[#4]

Sorry missed to reply couple of your other questions..

I would like to calculate three move gaps.. and i would like to do it in the script and use them in UI... Yes  I would like to calculate it for Cranes wise .. crane1 crane 2 etc.. i gave only once crane in the excel i gave to be simpler.

thanks

[#5]

But you need 1 difference for each shift or 1 difference for each shift+person?

regards

Darek

[#6]

Hi i would like to calc three shift moves differences...

[martinpedersen]

Hi

I have tried to solve the issue in the load script with something like this:

TimeTable:
Load
RowNo() as %RecId,
  QC & chr(124) & Date as %Key,
  If(Hour>=6 and Hour<18, 1, 2) as Shift,
    QC, [Completion Time], Moves, Date, Hour;
Load
  date(floor(Timestamp#([Completion Time], 'DD/MM/YYYY hh:mm:ss')), 'YYMMDD') as Date,
  Hour(Timestamp#([Completion Time], 'DD/MM/YYYY hh:mm:ss')) as Hour,
  Timestamp#([Completion Time], 'DD/MM/YYYY hh:mm:ss') as [Completion Time],
    QC,
    Moves
FROM Gap times.xls (biff, embedded labels, table is [Sheet1$]) where len(QC);


For i = 1 to 100
  GapTable:
  Load
  %Key,
  timestamp(max(If(Shift=1, [Completion Time]), $(i))) as Max,
  timestamp(min(If(Shift=2, [Completion Time]), $(i))) as Min,
  timestamp(max(If(Shift=1, [Completion Time]), $(i)))  & ' - ' & timestamp(min(If(Shift=2, [Completion Time]), $(i))) as Gap,
  $(i) as Rank
  Resident TimeTable Group By %Key;


Next

First load the data from Excel in 2 steps (First load and define formats, Second calculate Shift and create a Key/namefield.

Then i do a calculation of the 100 first max & Min pairs.

I have attached a file with the example.

[#8]

As a much uglier solution, could you not just split the data into two sets (before and after change), rank the before change as descending and then join on RowNo()?

[#9]

try this script.

result table should have all differences, even if you load data for many days.

regards

Darek

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep='.';

SET MoneyFormat='# ##0,00 zł;-# ##0,00 zł';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='sty;lut;mar;kwi;maj;cze;lip;sie;wrz;paź;lis;gru';

SET DayNames='Pn;Wt;Śr;Cz;Pt;So;N';

tab:

LOAD QC,

     [Completion Time],

     Moves

FROM

[http://community.qlik.com/servlet/JiveServlet/download/527948-106246/Gap%20times.xls]

(biff, embedded labels, table is Sheet1$);

tmp_tab:

load distinct num(date(left([Completion Time],10))) as date, 1 as tech Resident tab where Exists ([Completion Time]);

join

load num(MakeTime(shift,0,0)) as shift, 1 as tech;

load * inline

[shift

6

18];

tmp_inter1:

load date+shift as shift_date , RowNo() as inter Resident tmp_tab;

map_inter1:

Mapping load inter,shift_date Resident tmp_inter1;

tmp_inter_shifts:

load concat(chr(39)&text(shift_date)&chr(39),',') as sd Resident tmp_inter1 Group by 1;

LET v = FieldValue('sd',1);

drop tables tmp_tab,tmp_inter1,tmp_inter_shifts;

//load 1 as start,1 as y AutoGenerate(0);

for Each shift in $(v)

//let date_sh=text($(shift));

b4:

First 3

load [Completion Time] as start , if(mod(rowno(),3)=0,3,mod(rowno(),3)) as distance, date($(shift))&' '&Time($(shift)) as shift Resident tab where num([Completion Time])<= $(shift) Order by [Completion Time] desc;

aft:

First 3

load [Completion Time] as end , if(mod(rowno(),3)=0,3,mod(rowno(),3)) as distance, date($(shift))&' '&Time($(shift)) as shift Resident tab where num([Completion Time])> $(shift) Order by [Completion Time] asc;

NEXT;

Join (aft) LOAD * Resident b4;

result:

load *, time(end-start) as diff;

load * Resident aft where start<>Null() and end <> null();

drop tables b4,aft;

[#10]

Thanks to both of you. I have given a try but it dnt work for me.

Let me explain you more clearly about my requirement.

I have a carrier and lets say 3 cranes are working for it to unload (crane1,crane2,crane3).I have two shifts per day. I mean at 6AM and 18(eveing 6PM) basically its 24 hour format clock.

Lets say Crane1 is working for 22hours to unload then it will cover both the shifts.I would like to find the impact of the shift chnage basically how long it has taken for the crane opreator to make the move once the shift has changed so that i could understand if there is any delay made by the operator.

For this i would like to know the fist,second and third time after the shift change as well as before the shift change.so that if i subtract the    last first value of time (before shift change)- firstvalue of time(after shift change) = Move1

last  second value of time (before shift change)- second value of time(after shift change) = Move2

last third value of time (before shift change)- third value of time(after shift change) = Move3

This should happen similarly for all the carrier wise and Crane wise.(as many as cranes worked for teh carrier.

PFA excel for test data. Any Crane which has both the shifts should calc the moves based on its worked timings.

Please ignore the columns

Firstmove_Max

Firstmove_Min

Secondmove_Max

which i have calc which are not working..

My result table format is shown in excel.

Any help would be grealty appreciated.

Thanks in advance.