Logical Condition

[rakeshkumar1890]

Hello everyone,

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 on longestime, check category with given preference. 

Check 2 :Per OrderLine on longestime, check category with given preference. If same category more than 1 then check into OrderNo counts. Maximum no. of counts should be output

Table:

OrderLine	Parts	LongestTime	Category	OrderNo.
CET0578	P24	13	supplier issue	304
CET0578	P24	13	forecast	305
CET0578	P05	18	Supply Issue	304
CET0578	P19	18	forecast	100
NIM0578	P24	13	supplier issue	304
NIM0578	P24	13	forecast	305
NIM0578	P24	18	supplier issue	304
NIM0578	P24	18	invalid reason code	246
NIM0578	P24	18	forecast	304
PCT0020	P18	10	supplier issue	685
PCT0020	P07	18	forecast	370
PCT0020	P41	18	supplier issue	938
PCT0020	P63	18	invalid reason code	938
PCT0020	P72	18	forecast	938
PCT0020	P92	18	forecast	370
PCT0020	P25	10	supplier issue	685
PCT0020	P10	18	Supply Issue	290
PCT0020	P14	18	supplier issue	564
PCT0020	P30	18	invalid reason code	564
PCT0020	P56	18	Supply Issue	564
PCT0020	P90	18	Supply Issue	290
PCT0950	P01	3	supplier issue	685
PCT0950	P03	2	supplier issue	938
PCT0950	P02	5	forecast	200
PCT0950	P04	5	invalid reason code	938
PCT0950	P05	5	forecast	938
PCT0950	P06	5	forecast	370
PCT0389	P01	3	Bcom	685
PCT0389	P03	4	supplier issue	938
PCT0389	P63	6	invalid reason code	938
PCT0389	P19	12	NA	938
PCT0389	P31	12	Maintenance	938
PCT0389	P22	12	Daily	370

Output Result:     Logics

OrderLine	Parts	LongestTime	Category	OrderNo.	Remark
CET0578	P05	18	Supply Issue	304	Result was came due to first preference category - Supply Issue
NIM0578	P24	18	forecast	304	Result was came due to second preference category - forecast
PCT0020	P56	18	Supply Issue	564	Result was came due to more than 1 Supply Issue on each orderline PCT0020 at longest time 18,  then I checked to Order No. 564 more than others on longest time ( I checked only "Supply Issue" not to forecast because Supply Issue is first preference
PCT0950	P05	5	forecast	938	Result was came due to more than 1 forecast on each orderline PCT0950 at longest time 5,  then I checked to Order No. 564 more than others in forecast on longest time
PCT0389	P19	12	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	12	Maintenance	938
PCT0389	P22	12	Daily	370

Thanks to everyone for your input. I am waiting for the output result.

Please feel to reply if any doubts.

Kind Regards

[Taoufiq_Zarra]

@rakeshkumar1890  can you elaborate when we have more than one Supply Issue or forecast

PCT0020	P56	18	Supply Issue	564	Result was came due to more than 1 Supply Issue on each orderline PCT0020 at longest time 18,  then I checked to Order No. 564 more than others on longest time ( I checked only "Supply Issue" not to forecast because Supply Issue is first preference
PCT0950	P05	5	forecast	938	Result was came due to more than 1 forecast on each orderline PCT0950 at longest time 5,  then I checked to Order No. 564 more than others in forecast on longest time

and to be sure Supply Issue not supplier issue ?

[rakeshkumar1890]

Thanks Taoufiq for your input.

Yes it may be more Supply Issue or forecast for each OrderLine, Parts and OrderNo.

Supply Issue and supplier issue are differ. These are two category

I want to more filter to raw data. Suppose there is no any longest time. Then in this case need logic on Category preference

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

[rakeshkumar1890]

How we can set the logic--

1. First distinct OrderLine then check category for each part no. if found Supply Issue then check how many part no has this category if one time then stop the logic. if more than one time for each part no then we have to check OrderNo. in which highest count of orderno for supply issue.

2. Similarly for forcast.

3. If both(Supply Issue and forecast) are exist in same Orderline then first preference should be - Supply Issue then proceed  with 1 above point.

4. if both are not exist then no logic further it should all partno and category.

[Taoufiq_Zarra]

@rakeshkumar1890  One option :

Input:

LOAD * INLINE [
    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
];
left join load OrderLine,concat(Category,'_') as chek1 resident Input group by OrderLine;

[SupplyIssueTmp]:
noconcatenate
load * resident Input where Match(Category,'Supply Issue')>0 ;

[SupplyIssue]:
noconcatenate

load OrderLine, FirstSortedValue(Parts,-OrderNo) as Parts, FirstSortedValue(Category,-OrderNo) as Category, max(OrderNo) as OrderNo resident SupplyIssueTmp group by OrderLine;

drop table SupplyIssueTmp;

[forecastTmp]:
noconcatenate
load * resident Input where (Match(Category,'forecast')>0 and substringcount(chek1,'Supply Issue')=0) ;


[forecast]:
concatenate(SupplyIssue)

load OrderLine, FirstSortedValue(Parts,-OrderNo) as Parts, FirstSortedValue(Category,-OrderNo) as Category, max(OrderNo) as OrderNo resident forecastTmp group by OrderLine;


drop table forecastTmp;


output:
concatenate(SupplyIssue)
load * resident Input where wildMatch(chek1,'*forecast*','*Supply Issue*')=0;


drop table Input;

drop fields chek1;

output:

[rakeshkumar1890]

Thanks you so much @Taoufiq_Zarra . 

Let me check on my actual data.

[rakeshkumar1890]

@Taoufiq_Zarra : As I checked script you are using Max(OrderNo).

Basically I don't want max(OrderNo).  I need highest count of OrderNo,  lets assume data,

Here 100 came 2 times. but in this 100 category preference is "forecast". resultant highlighted rows.

OrderLine	Parts	Category	OrderNo	Changed order
PCT0950	P02	forecast	200
PCT0950	P04	invalid reason code	100	Instead of 938 using 100
PCT0950	P05	forecast	100	Instead of 938 using 100
PCT0950	P06	forecast	370

[Taoufiq_Zarra · Accepted solution]

@rakeshkumar1890  and like this ?

Input:

LOAD * INLINE [
    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
    PCT0389, P22, Daily, 370
    PCT0389, P31, Maintenance, 938
    PCT0389, P19, NA, 938
    PCT0950, P02, forecast, 200
    PCT0950, P04, invalid reason code, 100
    PCT0950, P05, forecast, 100
    PCT0950, P06, forecast, 370
];


left join load OrderLine,concat(Category,'_') as chek1 resident Input group by OrderLine;

left join (Input) load OrderLine,OrderNo,count(OrderNo) as countOrderNo resident Input group by OrderLine,OrderNo;


[SupplyIssueTmp]:
noconcatenate
load * resident Input where Match(Category,'Supply Issue')>0 ;

[SupplyIssue]:
noconcatenate

load OrderLine, FirstSortedValue(Parts,-countOrderNo) as Parts, FirstSortedValue(Category,-countOrderNo) as Category, FirstSortedValue(OrderNo,-countOrderNo) as OrderNo resident SupplyIssueTmp group by OrderLine;

drop table SupplyIssueTmp;


[forecastTmp]:
noconcatenate
load * resident Input where (Match(Category,'forecast')>0 and substringcount(chek1,'Supply Issue')=0) ;


[forecast]:
concatenate(SupplyIssue)

load OrderLine, FirstSortedValue(Parts,-countOrderNo) as Parts, FirstSortedValue(Category,-countOrderNo) as Category, FirstSortedValue(OrderNo,-countOrderNo) as OrderNo  resident forecastTmp group by OrderLine;


drop table forecastTmp;


output:
concatenate(SupplyIssue)
load * resident Input where wildMatch(chek1,'*forecast*','*Supply Issue*')=0;


drop table Input;

drop fields chek1,countOrderNo;

[rakeshkumar1890]

Big thanks @Taoufiq_Zarra . I got the same solution