Create Category Field Based on Values

[GMadd]

I wrote the script below to help measure on-time shipping. It looks at values given and determines if the shipment was shipped late and assigns a category. Everything is working so far but when I place the last line of code it just makes every shipment late.

If([Act. Gds Mvmnt Date] > [Goods Issue Date], 'Late') 

Below is the script I wrote.

//Category

If([Act. Gds Mvmnt Date] <= [Goods Issue Date], 'On-Time',
If([Delay Reason Code - Header] = 13, 'Customer Delay/System Issue',
If([Delay Reason Code - Header] = 356 or
[Delay Reason Code - Header] = 01 or
[Delay Reason Code - Header] = 02 or
[Delay Reason Code - Header] = 03 or
[Delay Reason Code - Header] = 04 or
[Delay Reason Code - Header] = 05, 'Carrier Issue',
If([Delay Reason Code - Header] = 06, 'Back Order',
If([Delay Reason Code - Header] = 07, 'Carrier Assignment Delay',
If([Delay Reason Code - Header] = 08, 'Late',
If([Delay Reason Code - Header] = 09, 'Late Carrier Assignment',
If([Delay Reason Code - Header] = 10, 'Late Order Entry',
If([Delay Reason Code - Header] = 11, 'Warehouse Inventory Issue',
If([Delay Reason Code - Header] = 12, 'Material Quality Issue',
If([Act. Gds Mvmnt Date] > [Goods Issue Date] and 'Late_CPUX' = '' and 'PickUpApp?' = 'No' and 'Credit_Hold' = 'VERKBELEG', 'Credit Hold',
If([Created On] >= [Goods Issue Date], 'Back Order',
Pick(Match(Route,'CPU000', 'CPU001', 'CPU002'),
IF("Act. Gds Mvmnt Date">"Goods Issue Date",'LatePickUp','On Time'),
'On Time',
'On Time')))))))))))))

What am I doing wrong?

[robert_mika]

is the IF line above the //Category apart of the expression?

Try thi 

If([Act. Gds Mvmnt Date] <= [Goods Issue Date], 'On-Time',
If([Delay Reason Code - Header] = 13, 'Customer Delay/System Issue',
If(Match([Delay Reason Code - Header], '356', '01', '02', '03', '04', '05'), 'Carrier Issue',
If([Delay Reason Code - Header] = '06', 'Back Order',
If([Delay Reason Code - Header] = '07', 'Carrier Assignment Delay',
If([Delay Reason Code - Header] = '08', 'Late',
If([Delay Reason Code - Header] = '09', 'Late Carrier Assignment',
If([Delay Reason Code - Header] = '10', 'Late Order Entry',
If([Delay Reason Code - Header] = '11', 'Warehouse Inventory Issue',
If([Delay Reason Code - Header] = '12', 'Material Quality Issue',
If([Act. Gds Mvmnt Date] > [Goods Issue Date] and 'Late_CPUX' = '' and 'PickUpApp?' = 'No' and 'Credit_Hold' = 'VERKBELEG', 'Credit Hold',
If([Created On] >= [Goods Issue Date], 'Back Order',
Pick(Match(Route,'CPU000', 'CPU001', 'CPU002'),
If([Act. Gds Mvmnt Date] > [Goods Issue Date],'LatePickUp','On Time'),
'On Time',
'On Time')))))))))))))

[GMadd · Accepted solution]

Guess I wasn't clear. The way I want it to work is for all the script above to do it's thing and if none of it applies have a script then look at the [Act. Gds Mvmnt Date] field to see if it is greater than the [Goods Issue Date] field and if it is give it a category of Late. 

I put the script below as the last line in my category section 

If([Act. Gds Mvmnt Date] > [Goods Issue Date], 'Late')

The script above should should give the shipment a category of "Late" if none of the other script applies and the Actual Goods Movement date is greater that the Goods Issue Date. 

Currently it is just leaving those shipments with out a category

Below is my full script that is categorizing each shipment.

//Category

If([Act. Gds Mvmnt Date] <= [Goods Issue Date], 'On-Time',
If([Delay Reason Code - Header] = 13, 'Customer Delay/System Issue',
If([Delay Reason Code - Header] = 356 or
[Delay Reason Code - Header] = 01 or
[Delay Reason Code - Header] = 02 or
[Delay Reason Code - Header] = 03 or
[Delay Reason Code - Header] = 04 or
[Delay Reason Code - Header] = 05, 'Carrier Issue',
If([Delay Reason Code - Header] = 06, 'Back Order',
If([Delay Reason Code - Header] = 07, 'Carrier Assignment Delay',
If([Delay Reason Code - Header] = 08, 'Late',
If([Delay Reason Code - Header] = 09, 'Late Carrier Assignment',
If([Delay Reason Code - Header] = 10, 'Late Order Entry',
If([Delay Reason Code - Header] = 11, 'Warehouse Inventory Issue',
If([Delay Reason Code - Header] = 12, 'Material Quality Issue',
If([Act. Gds Mvmnt Date] > [Goods Issue Date] and 'Late_CPUX' = '' and 'PickUpApp?' = 'No' and 'Credit_Hold' = 'VERKBELEG', 'Credit Hold',
If([Created On] >= [Goods Issue Date], 'Back Order',
Pick(Match(Route,'CPU000', 'CPU001', 'CPU002'),
IF("Act. Gds Mvmnt Date">"Goods Issue Date",'LatePickUp','On Time'),
'On Time',
'On Time',
If([Act. Gds Mvmnt Date] > [Goods Issue Date], 'Late'))))))))))))))
as Category,

[rubenmarin1 · Accepted solution]

Hi, you have the last 'If' as the 4th parameter of Pick(), and the Match() will return a value between 0 and 3, so it will never go the 4th option.

An option could be to set and 'If' before the Pick(Match(, to confirm it has one of the expected options:

If(Match(Route,'CPU000', 'CPU001', 'CPU002')
  ,Pick(Match(Route,'CPU000', 'CPU001', 'CPU002')
    ,If("Act. Gds Mvmnt Date">"Goods Issue Date"
      ,'LatePickUp'
      ,'On Time') // Pick-1
    ,'On Time' // Pick-2
    ,'On Time')  //Pick-3
  ,If([Act. Gds Mvmnt Date] > [Goods Issue Date]
    ,'Late')))))))))))))
as Category,

Or add one to the Match() result, so if none of the options applies, it will go to the first option of pick:

Pick(Match(Route,'CPU000', 'CPU001', 'CPU002')+1
  ,If([Act. Gds Mvmnt Date] > [Goods Issue Date]
    ,'Late') // Pick-1
  ,If("Act. Gds Mvmnt Date">"Goods Issue Date"
    ,'LatePickUp'
    ,'On Time') // Pick-2
  ,'On Time' // Pick-3
  ,'On Time')  //Pick-4
))))))))))))
as Category,

I would also reccomend to use some indentation and comments that helps to follow which part is inside another