Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Need your help to find out this logical condition on script.
Perference:
1. Check Longest Period(difference max(Plandate)-min(Plandate) on CTS and Part. That Is MCP
2. Check Longest Period(difference max(Plandate)-min(Plandate) on CTS and Part and Category . That Is Rootcause.
3. if Longest MCP and Longest Period on Category are same then go to preference Category. Example (CTS - 300)
4. if Longest MCP and Longest Period on Category are same then go to highest count of Solima(CTS -600)
Raw Data: Exclude (OutputReslut- Need to calculate on script)
CTS | Part | Category | PlanDate | Solima | OutputMaxPart | OutputMaxCategory | OutputSolimaCount | |
100 | P-123 | ww supply issue | 7/21/2020 | 1800 | 6 | 6 | ||
100 | P-123 | ww supply issue | 7/27/2020 | 1800 | 6 | 6 | ||
100 | L-123 | forecast | 7/21/2020 | 1900 | 7 | 7 | MCP | |
100 | L-123 | forecast | 7/28/2020 | 1900 | 7 | 7 | MCP | |
200 | M-123 | forecast | 9/1/2020 | 1500 | 8 | 8 | MCP | |
200 | M-123 | forecast | 9/9/2020 | 1500 | 8 | 8 | MCP | |
200 | O-123 | invalid reason code | 9/9/2020 | 1600 | 0 | 0 | ||
200 | P-123 | daily pull/jit process | 9/10/2020 | 1600 | 0 | 0 | ||
200 | Q-123 | strategic inventory | 9/10/2020 | 1700 | 0 | 0 | ||
300 | P-456 | forecast | 8/18/2020 | 300 | 9 | 9 | MCP | |
300 | P-456 | forecast | 8/27/2020 | 300 | 9 | 9 | MCP | |
300 | L-123 | strategic inventory | 8/18/2020 | 700 | 9 | 9 | ||
300 | L-123 | strategic inventory | 8/27/2020 | 700 | 9 | 9 | ||
400 | A-123 | forecast | 8/10/2020 | 2200 | 17 | 17 | 1 | |
400 | A-123 | forecast | 8/11/2020 | 2300 | 17 | 17 | 1 | |
400 | A-123 | forecast | 8/27/2020 | 2400 | 17 | 17 | 1 | |
400 | B-198 | ww supply issue | 8/10/2020 | 900 | 17 | 17 | 2 | MCP |
400 | B-198 | ww supply issue | 8/11/2020 | 900 | 17 | 17 | 2 | MCP |
400 | B-198 | ww supply issue | 8/27/2020 | 800 | 17 | 17 | 1 | |
500 | C-181 | strategic inventory | 8/1/2020 | 290 | 14 | 7 | ||
500 | C-181 | strategic inventory | 8/2/2020 | 291 | 14 | 7 | ||
500 | C-181 | strategic inventory | 8/8/2020 | 292 | 14 | 7 | ||
500 | C-181 | daily pull/jit process | 8/3/2020 | 293 | 14 | 12 | MCP | |
500 | C-181 | daily pull/jit process | 8/4/2020 | 294 | 14 | 12 | MCP | |
500 | C-181 | daily pull/jit process | 8/5/2020 | 297 | 14 | 12 | MCP | |
500 | C-181 | daily pull/jit process | 8/6/2020 | 298 | 14 | 12 | MCP | |
500 | C-181 | daily pull/jit process | 8/15/2020 | 299 | 14 | 12 | MCP | |
600 | D-367 | forecast | 8/1/2020 | 340 | 7 | 7 | 2 | MCP |
600 | D-367 | forecast | 8/2/2020 | 340 | 7 | 7 | 2 | MCP |
600 | D-367 | forecast | 8/8/2020 | 341 | 7 | 7 | 1 | |
600 | S-983 | forecast | 8/1/2020 | 561 | 7 | 7 | 1 | |
600 | S-983 | forecast | 8/2/2020 | 281 | 7 | 7 | 1 | |
600 | S-983 | forecast | 8/8/2020 | 351 | 7 | 7 | 1 | |
700 | D-367 | forecast | 8/1/2020 | 234 | 7 | 7 | 1 | MCP |
700 | D-367 | forecast | 8/2/2020 | 340 | 7 | 7 | 1 | |
700 | D-367 | forecast | 8/8/2020 | 341 | 7 | 7 | 1 | |
700 | S-983 | forecast | 8/1/2020 | 561 | 7 | 7 | 1 | |
700 | S-983 | forecast | 8/2/2020 | 281 | 7 | 7 | 1 | |
700 | S-983 | forecast | 8/8/2020 | 351 | 7 | 7 | 1 |
Output Result :
CTS | MCP | RootCause |
100 | L-123 | forecast |
200 | M-123 | forecast |
300 | P-456 | forecast |
400 | B-198 | ww supply issue |
500 | C-181 | daily pull/jit process |
600 | D-367 | forecast |
@rakeshkumar1890 try below
Data:
LOAD CTS,
Part,
Category,
PlanDate,
Solima
FROM Table;
Left Join(Data)
LOAD CTS,
Part,
max(PlanDate)-min(PlanDate) as [Longest MCP]
Resident Data
Group by CTS,
Part;
Left Join(Data)
LOAD CTS,
Part,
Category,
max(PlanDate)-min(PlanDate) as [Longest Time]
Resident Data
Group by CTS,
Part,
Category;
Left Join(Data)
LOAD CTS,
Part,
Category,
Solima,
Count(Solima) as cnt_solima
Resident Data
Group by CTS,
CTS,
Part,
Category,
Solima;
Left Join(Data)
LOAD CTS,
max([Longest MCP]) as Max_Longest_MCP,
max([Longest Time]) as Max_Longest_Time
Resident Data
Group by CTS;
T1:
LOAD Distinct CTS,
Part as MCP,
Category as RootCause,
lower(trim(Category)) as RootCause_key,
cnt_solima
Resident Data
where(Max_Longest_MCP=[Longest MCP] and Max_Longest_Time=[Longest Time])
order by CTS,Part,PlanDate;
DROP Table Data;
Left Join(T1)
LOAD CTS,
max(cnt_solima) as max_cnt_solima
Resident T1
Group by CTS;
Left Join(T1)
LOAD CTS,
Concat(DISTINCT RootCause_key,'|') as All_RootCause_key
Resident T1
Group by CTS;
T2:
NoConcatenate
LOAD *,
if(RowNo()=1,1,Peek(Num)+1) as Num,
if(wildmatch(All_RootCause_key,'*ww supply issue*') ,'ww supply issue',
if(wildmatch(All_RootCause_key,'*forecast*'),'forecast',All_RootCause_key)) as Preference_RootCause
Resident T1;
DROP Table T1;
Final:
LOAD CTS,
MCP,
Num,
RootCause
Resident T2
where Preference_RootCause=RootCause_key and cnt_solima=max_cnt_solima;
DROP Table T2;
Inner Join(Final)
LOAD CTS,
min(Num) as Num
Resident Final
Group by CTS;
DROP Field Num;
@rakeshkumar1890 how logic for CTS -100 & 200 goes?
@Kushal_Chawda Due to longest Period 7,8 for CTS -100 & 200
I am not getting the correct result as I posted above:
MST_B05_Temp:
LOAD CTS,Part,Category,Date#(PlanDate,'M/D/YYYY')as PlanDate, Solima INLINE [
CTS,Part,Category,PlanDate,Solima
100, P-123, ww supply issue, 7/21/2020, 1800
100, P-123, ww supply issue, 7/27/2020, 1800
100, L-123, forecast, 7/21/2020, 1900
100, L-123, forecast, 7/28/2020, 1900
200, M-123, forecast, 9/1/2020, 1500
200, M-123, forecast, 9/9/2020, 1500
200, O-123, invalid reason code, 9/9/2020, 1600
200, P-123, daily pull/jit process, 9/10/2020, 1600
200, Q-123, strategic inventory, 9/10/2020, 1700
300, P-456, forecast, 8/18/2020, 300
300, P-456, forecast, 8/27/2020, 300
300, L-123, strategic inventory, 8/18/2020, 700
300, L-123, strategic inventory, 8/27/2020, 700
400, A-123, forecast, 8/10/2020, 2200
400, A-123, forecast, 8/11/2020, 2300
400, A-123, forecast, 8/27/2020, 2400
400, B-198, ww supply issue, 8/10/2020, 900
400, B-198, ww supply issue, 8/11/2020, 900
400, B-198, ww supply issue, 8/27/2020, 800
500, C-181, strategic inventory, 8/1/2020, 290
500, C-181, strategic inventory, 8/2/2020, 291
500, C-181, strategic inventory, 8/8/2020, 292
500, C-181, daily pull/jit process, 8/3/2020, 293
500, C-181, daily pull/jit process, 8/4/2020, 294
500, C-181, daily pull/jit process, 8/5/2020, 297
500, C-181, daily pull/jit process, 8/6/2020, 298
500, C-181, daily pull/jit process, 8/15/2020, 299
600, D-367, forecast, 8/1/2020, 340
600, D-367, forecast, 8/2/2020, 340
600, D-367, forecast, 8/8/2020, 341
600, S-983, forecast, 8/1/2020, 561
600, S-983, forecast, 8/2/2020, 281
600, S-983, forecast, 8/8/2020, 351
700, D-367, forecast, 8/1/2020, 234
700, D-367, forecast, 8/2/2020, 340
700, D-367, forecast, 8/8/2020, 341
700, S-983, forecast, 8/1/2020, 561
700, S-983, forecast, 8/2/2020, 281
700, S-983, forecast, 8/8/2020, 351
];
LEFT JOIN(MST_B05_Temp)
Load
CTS,
Part,
Category,
min(PlanDate) as MWT05_Min_ReplanforCTS,
max(PlanDate) as MWT05_Max_ReplanforCTS,
max(PlanDate)-min(PlanDate) AS MWT05_CTS_MST_01
Resident MST_B05_Temp
Group BY CTS,Part,Category;
LEFT JOIN(MST_B05_Temp)
load CTS,
max(MWT05_CTS_MST_01) as MWT05_CTS_MST_MAX
Resident MST_B05_Temp
GROUP BY CTS;
MST_B05: /// Got data with Longest Period
NoConcatenate
Load *
Resident MST_B05_Temp
Where MWT05_CTS_MST_01 = MWT05_CTS_MST_MAX
;
Drop Table MST_B05_Temp;
left join
load CTS,concat(Category,'_') as chek1
resident MST_B05 group by CTS;
left join (MST_B05) /// Finding Highest Count of Solima
load CTS,Part,Category,Solima,
count(Solima) as Solimacount
resident MST_B05
group by CTS,Part,Category,Solima;
[SupplyIssueTmp]: // Checking Category for "ww supply issue"
noconcatenate
load *
resident MST_B05
where Match(Category,'ww supply issue')>0;
[MST_BRD05]: // Getting data for "ww supply issue" with highest count of solima
noconcatenate
load CTS,
FirstSortedValue(Part,-Solimacount) as Part,
FirstSortedValue(Category,-Solimacount) as Category,
FirstSortedValue(Solima,-Solimacount) as Solima
resident SupplyIssueTmp
group by CTS;
drop table SupplyIssueTmp;
[forecastTmp]: // Checking Category for "forecast"
noconcatenate
load *
resident MST_B05
where (Match(Category,'forecast')>0 and substringcount(chek1,'ww supply issue')=0);
[forecast]: // Getting data for "forecast" with highest count of solima
concatenate(MST_BRD05)
load CTS,
FirstSortedValue(Part,-Solimacount) as Part,
FirstSortedValue(Category,-Solimacount) as Category,
FirstSortedValue(Solima,-Solimacount) as Solima
resident forecastTmp
group by CTS;
drop table forecastTmp;
MST_B05_Final: // // Getting data for other Categories.
concatenate(MST_BRD05)
load *
resident MST_B05
where Match(chek1,'forecast','ww supply issue')=0;
drop table MST_B05;
drop fields chek1,Solimacount, PlanDate,MWT05_Min_ReplanforCTS,MWT05_Max_ReplanforCTS;
EXIT SCRIPT;
@rakeshkumar1890 how MCP of 700 is selected? Because for longest period for all same category solima is also unique. So what is the criteria to pick?
@Kushal_Chawda for 700 it should be first row. whatever at loading time.
@rakeshkumar1890 try below
Data:
LOAD CTS,
Part,
Category,
PlanDate,
Solima
FROM Table;
Left Join(Data)
LOAD CTS,
Part,
max(PlanDate)-min(PlanDate) as [Longest MCP]
Resident Data
Group by CTS,
Part;
Left Join(Data)
LOAD CTS,
Part,
Category,
max(PlanDate)-min(PlanDate) as [Longest Time]
Resident Data
Group by CTS,
Part,
Category;
Left Join(Data)
LOAD CTS,
Part,
Category,
Solima,
Count(Solima) as cnt_solima
Resident Data
Group by CTS,
CTS,
Part,
Category,
Solima;
Left Join(Data)
LOAD CTS,
max([Longest MCP]) as Max_Longest_MCP,
max([Longest Time]) as Max_Longest_Time
Resident Data
Group by CTS;
T1:
LOAD Distinct CTS,
Part as MCP,
Category as RootCause,
lower(trim(Category)) as RootCause_key,
cnt_solima
Resident Data
where(Max_Longest_MCP=[Longest MCP] and Max_Longest_Time=[Longest Time])
order by CTS,Part,PlanDate;
DROP Table Data;
Left Join(T1)
LOAD CTS,
max(cnt_solima) as max_cnt_solima
Resident T1
Group by CTS;
Left Join(T1)
LOAD CTS,
Concat(DISTINCT RootCause_key,'|') as All_RootCause_key
Resident T1
Group by CTS;
T2:
NoConcatenate
LOAD *,
if(RowNo()=1,1,Peek(Num)+1) as Num,
if(wildmatch(All_RootCause_key,'*ww supply issue*') ,'ww supply issue',
if(wildmatch(All_RootCause_key,'*forecast*'),'forecast',All_RootCause_key)) as Preference_RootCause
Resident T1;
DROP Table T1;
Final:
LOAD CTS,
MCP,
Num,
RootCause
Resident T2
where Preference_RootCause=RootCause_key and cnt_solima=max_cnt_solima;
DROP Table T2;
Inner Join(Final)
LOAD CTS,
min(Num) as Num
Resident Final
Group by CTS;
DROP Field Num;
@Kushal_Chawda Big Thanks Kush, Let me check on real data.
Thank you so much @Kushal_Chawda.