Applying a change to a group of records depending on one record

[#1]

Hi Everyone,

I'm trying to load in a set of data regarding deliveries where you have an Order Number and each Order Number can have multiple Order Lines.

Each line has a Delivery Failed Flag to indicate whether the delivery was sucessful or not.

Is there a way in the script to mark the whole order as failed if any one of the lines fail?

Eg.

[Order Num]   [Line Num]  [Failed?]

100               1                 Yes

100               2                 No

101               1                 No

102               1                 No

102               2                 No

102               3                 Yes

I know i need to create a new field to store the [Order Fail] record am and guessing it'll require a "Group" function but can't figure out how to do this.

Thanks,

Nick

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this approach.

     Order:

     Load

          [Order Num],

          [Line Num],

          if([Failed?]='Yes',1,0) as Failed

     From..

     OrderStatus:

     Load

          [Order Num],

          if(Sum(Failed)>=1,'Yes','No') as OrderFailed

     Resident

     Order Group By [Order Num];

Hope it helps

Celambarasan

[#3]

Thanks very much Celambarasan!

Exactly what i needed!

Nick