Need your help to find out this logical condition on script.
Perference:
First Preference on Category : Supply Issue
Second Preference on Category : forecast
Third Preference on Category : Others categories
Check 1 :Per OrderLine, check category with given preference.
Check 2 :Per OrderLine, check category with given preference. If same category more than 1 then check into OrderNo counts. Maximum no. of counts should be output
We have the raw data like below:
OrderLine
Parts
Category
OrderNo
CET0578
P19
forecast
100
CET0578
P05
Supply Issue
304
NIM0578
P24
forecast
304
NIM0578
P24
invalid reason code
246
NIM0578
P24
supplier issue
304
PCT0020
P07
forecast
370
PCT0020
P92
forecast
370
PCT0020
P72
forecast
938
PCT0020
P30
invalid reason code
564
PCT0020
P63
invalid reason code
938
PCT0020
P14
supplier issue
564
PCT0020
P41
supplier issue
938
PCT0020
P10
Supply Issue
290
PCT0020
P90
Supply Issue
290
PCT0020
P56
Supply Issue
564
PCT0950
P02
forecast
200
PCT0950
P06
forecast
370
PCT0950
P05
forecast
938
PCT0950
P04
invalid reason code
938
PCT0389
P22
Daily
370
PCT0389
P31
Maintenance
938
PCT0389
P19
NA
938
Output result:
OrderLine
Parts
Category
OrderNo
Remark
CET0578
P05
Supply Issue
304
Result was came due to first preference category - Supply Issue
NIM0578
P24
forecast
304
Result was came due to second preference category - forecast
PCT0020
P56
Supply Issue
564
Result was came due to more than 1 Supply Issue on each orderline PCT0020, then I checked to Order No. 564 more than others ( I checked only "Supply Issue" not to forecast because Supply Issue is first preference
PCT0950
P05
forecast
938
Result was came due to more than 1 forecast on each orderline PCT0950, then I checked to Order No. 564 more than others in forecast.
PCT0389
P19
NA
938
Result was came due to third preference -- Checked Supply Issue or Forecast are there or not, If not then nothing to check.
PCT0389
P31
Maintenance
938
PCT0389
P22
Daily
370
Thanks in advance to everyone for your input. I am waiting for the output result.
