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]

Hi lalitha,

i think, there is no technical problem but rather problem with understanding your needs.

And, even now, after your additional explanation i still cant be sure how it should to work.

SO, i will ask some questions:

1.

Carrier1	26/04/2014 05:50:09	10635951
Carrier1	26/04/2014 05:51:41	10635975
Carrier1	26/04/2014 05:51:41	10635976
Carrier1	26/04/2014 05:53:31	10635999
Carrier1	26/04/2014 05:53:31	10636000
Carrier1	26/04/2014 06:10:01	10636000
Carrier1	26/04/2014 06:15:01	10636000
Carrier1	26/04/2014 06:14:01	10636000

Move 1 = 26/04/2014  06:10:01 - 26/04/2014  05:53:31
Move2 = 26/04/2014  06:15:01  - 26/04/2014  05:51:41
Move3 = 26/04/2014  06:14:01  - 26/04/2014  05:50:09

You used 05:53:31, 05:51:41, 05:50:09 as rows before shift. But in fact it is 1'st, 3'rd and 5'th row in this table (there are 2 rows with exactly the same time values). Should rows be distincted by time value? Only before shift, or also after shift?

2. In your earlier xls file version you had column "QC" with values Crane1:

I asked if you should regard any other information (like for example QC) in your matvhing, but I missed your earlier answer for that question . Now i understand, you need match rows of each crane (which is now Carrier column) separately....

3. what with columns:

ops_mve_gkey

che_qc_name

Are they important in matching or in result?

4. What is shift type?

I can find in your excel values Shift-6 and Shift-13. Shouldn't it be shift-18?

regards

Darek

[#3]

Hi Darek,

Sorry if i confused you..

1. The three distinct values needs to be considered before and after shift change.

2.Yes you are correct for each carrier wise,crane wise , need to find the three distinct values needs to be considered before and after shift change.

3.My Crane1 is nothing but QC05,QC02 etc.. I scrambled my data with Crane1 in the old excel.

4.Ops_Mve_gkey is the move id that has been made..

Hope this clears you..

Thanks for your time...

[#4]

Should we do anything with this Ops_Mve_gkey? Is it important from matching point of vview? Do you need it in appication?

And what is Shift-13 from your excel.

I found it under data:

Carrier Name	Crane	Shift type
Carrier	Crane1	Shift -6
	Crane2	Shift -6
	Crane3	Shift -6
	Crane3	Shift -13

regards

Darek

[#5]

sorry its a typo.. it  should be shift-18

[#6]

ops_move_gkey is a unique num that should not create a prb.. we take the count(ops_mve_gkey)

[#7]

So, please try to define all tables and fields you expect be loaded into application.

[martinpedersen · Accepted solution]

Hi Lalitha,

It not the most pretty solution (performance wise), but I think it will do the trick.

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);


//Create list of distinct Keys to evaluate. (Combination of QC & Date)
_KeyLoadTable:
Load Distinct %Key as _DistinctKey Resident TimeTable;
LET _KeyCounts = FieldValueCount('_DistinctKey'); //# distinct Keys in datamodel


//Loop trough each key
For x = 1 to $(_KeyCounts)
  LET _CurrentKey = FieldValue('_DistinctKey', $(x));

  //Get ranks for 1 to 100.
  For i = 1 to 100 // If needed a a count of pairs from each key could be implemented as max instead of a static number.
  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,
  %Key & chr(124) & $(i) as Id
  Resident TimeTable where %Key = '$(_CurrentKey)' Group By %Key;
  Next
Next

//Remove Incomplete Gap Lines (Set to > 21 to allow ranks with only 1 shift)
Right Join
Load Id Resident GapTable where len(Gap) = 41;

Let _CurrentKey = Null();
Let _KeyCounts = Null();

Drop Table _KeyLoadTable;
//Drop Fiels %Key, Id, Max, Min; //These fields are not needed in the final model.

Exit script

I have added a loop for each Date & QC combination in the model.