Getting data from every phase

[tka_ovako]

Hello.

I need to get value from varius phase from our production. Now I can get values from min phase and max phase but how I get values from every phase number. That value is FinalTons. This should do in script.

ProductionWeek	ProductionDay	WorkCenterSKey	PhaseNo	EventEndDate	FinalTons	ScrapTons	EventStartDate
201724	2017-06-17 13:18:17.000	104	10	2017-06-17 19:19:42.000	4,79	NULL	2017-06-17 19:18:17.000
201724	2017-06-18 10:52:28.000	145	20	2017-06-19 15:33:50.000	4,79	NULL	2017-06-18 16:52:28.000
NULL	NULL	104	23	NULL	8,224	NULL	NULL
NULL	NULL	145	24	NULL	8,224	NULL	NULL
NULL	NULL	104	25	NULL	8,224	NULL	NULL
201725	2017-06-20 11:32:40.000	104	26	2017-06-20 17:32:47.000	3,445	NULL	2017-06-20 17:32:40.000
201725	2017-06-20 21:37:25.000	145	27	2017-06-22 03:36:31.000	3,445	NULL	2017-06-21 03:37:25.000
201726	2017-06-26 16:37:36.000	104	28	2017-06-26 22:39:07.000	3,445	NULL	2017-06-26 22:37:36.000
201725	2017-06-20 05:59:23.000	104	30	2017-06-20 11:59:32.000	4,79	NULL	2017-06-20 11:59:23.000
201725	2017-06-20 07:24:17.000	110	40	2017-06-20 13:39:37.000	2,395	NULL	2017-06-20 13:24:17.000
201725	2017-06-20 07:24:17.000	110	40	2017-06-27 01:02:33.000	5,84	NULL	2017-06-20 13:24:17.000
201726	2017-06-26 19:02:33.000	100	50	NULL	NULL	NULL	NULL
201726	2017-06-28 01:32:31.000	130	60	2017-06-28 07:32:31.000	8,224	0,011	2017-06-28 07:32:31.000
201726	2017-06-29 03:12:50.000	147	70	NULL	8,224	NULL	NULL

Now my script for min phaseNo is

left join (Production)
LOAD
ProductionOrderLine,
min(PhaseNo) as PhaseNo,
1 as PhaseNo_min
Resident Production
group by ProductionOrderLine;

I also have another question:

When I have two or more same WorkCenterSKey (for example 104) how I can sort them with smallest phaseNo? I mean that I need to find smallest phaseno that individual WorkCenterSKey get (for example 104) .

BR.

Timo

[avkeep01 · Accepted solution]

Hi Timo,

You could try adding Phase as a dimension in your script. Then you also need to add this in de group by part. But then there is no function used in the load. So I added SUM(1).

left join (Production)
LOAD
ProductionOrderLine,
PhaseNo as PhaseNo,
SUM(1) as PhaseNo_min
Resident Production
group by ProductionOrderLine, PhaseNo;

For the second part:

left join (Production)
LOAD
WorkCenterSKey,
MIN(PhaseNo) as MIN_PhaseNo,
Resident Production
group by WorkCenterSKey;