Changing Dimension depending on multiple conditions

[dseelam]

Hello All,

I have a file with Item, cust num,status and effective date this will be joining to shipments file with Item , cust num and ship date so I created a item unique itemcust key

now I want to create a Result field with closeout & exclcloseout  as values depending on multiple conditions 

1. If a Item column has value ALL all items to that cust are closeout
2. if a item has status mentioned as closeout and exclcloseout starting from that effective date they are closeout and exclcoseout status respectively
3. if a item has no status mentioned they all exclcloseout

attached files 

please help 

thanks,

D