Hello Sunny,
Thank you for response, i prepared a dummy data of 6 rows for now attached in the reply.
Following the script.
WeeklyCorridor:
Left join
LOAD
Week,
[Supplier Loc] & [Customer Loc] & Material as %key,
[Customer Loc] & Material as %key2,
Material,
[Material Description],
[Material Type],
Category,
if([Inventory Volume]='-',0,[Inventory Volume]) as [Inventory Volume],
if([Demand Volume (Eaches)]='-',0,[Demand Volume (Eaches)]) as [Demand Volume (Eaches)],
if(DFC='-',0,DFC) as DFC,
Brand,
Variant,
Lifecycle,
[Customer Loc],
[Customer Location Description],
[Customer Zone],
[Supplier Loc],
[Supplier Gov BU],
[Corridor Status],
[Min DFC],
[Max DFC],
if([Safety Days Supply]='-',0,[Safety Days Supply]) as [Safety Days Supply],
//Creation of rules
IF(((num([Min DFC])+ num([Max DFC]))<> 0) and num([Min DFC]) >= num([Max DFC]),'Reason- Min >= Max') as Rule1,
IF((num([Safety Days Supply]) + num([Max DFC])) <> 0 and num([Safety Days Supply]) >= num([Max DFC] ), 'Reason-SSD>Max') as Rule2,
IF(((num([Min DFC])+ num([Max DFC]))=0 AND num([Safety Days Supply]) <> 0),'Reason-SSD has value when Min & Max is zero') as Rule3,
If((num([Min DFC]) + num([Max DFC]))=0 and Lifecycle<>'Material Run Down','Reason-Min Max not set') as Rule4,
IF( Lifecycle='Material Run Down' and (num([Min DFC])+ num([Max DFC]))<>0, 'Reason-RunDown SKU Min & Max > Zero') as Rule5,
IF((num([Safety Days Supply])+num([Min DFC]))<>0 and num([Safety Days Supply])<= num([Min DFC]),'Reason-SSD <= Min') as Rule6,
IF([Safety Days Supply]='-' and num([Max DFC])>0, 'Reason-SSD not set') as Rule7,
IF([Safety Days Supply]='-' and (num([Min DFC])+ num([Max DFC]))=0, 'Min & Max = Zero') as Rule8,
if([Min DFC]='-' and [Max DFC]='-','Reason-Min Max not set') as Rule9,
// Inventory Position calcualtions as follows:
if(DFC = 0 and [Inventory Volume] < 0,'OOS',
if(DFC < [Min DFC] and [Inventory Volume] > 0, 'Low Stock',
if(DFC >= [Min DFC] and DFC <= [Max DFC], 'Intolerance',
if(DFC > [Max DFC], 'Overstock',
if(DFC = 0 and [Inventory Volume] = 0, 'NoDemand'))))) as Inventory_Position,
//Creation of action items based on rules created for planners.
IF(((num([Min DFC])+ num([Max DFC]))<> 0) and num([Min DFC]) >= num([Max DFC]),'Reason- Min >= Max',
IF((num([Safety Days Supply]) + num([Max DFC])) <> 0 and num([Safety Days Supply]) >= num([Max DFC] ), 'Reason-SSD>Max',
IF((num([Min DFC])=0 and num([Max DFC])=0 AND num([Safety Days Supply]) > 0),'Reason-SSD has value when Min & Max is zero',
If((num([Min DFC]) + num([Max DFC]))=0 and Lifecycle<>'Material Run Down','Reason-Min Max not set',
IF( Lifecycle='Material Run Down' and (num([Min DFC])+ num([Max DFC]))<>0 , 'Reason-RunDown SKU Min Max > Zero',
IF((num([Safety Days Supply])+num([Min DFC]))<>0 and num([Safety Days Supply])<= num([Min DFC]),'Reason-SSD <= Min',
IF([Safety Days Supply]='-' and num([Max DFC])>0, 'Reason-SSD Notdefined',
IF([Safety Days Supply]='-' and (num([Min DFC])+ num([Max DFC]))=0, 'Min&Max = Zero and SSD is blank',
if([Min DFC]='-' and [Max DFC]='-','Reason-Min Max not set',
if([Min DFC]='-', 'Min Not Set',
if([Max DFC]='-', 'Max Not Set', 'All Set'))))))))))) AS ActionItem
