topic Applying a change to a group of records depending on one record in QlikView

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

Wed, 15 Feb 2012 10:02:47 GMT

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

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

CELAMBARASAN — Wed, 15 Feb 2012 10:20:12 GMT

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

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

Wed, 15 Feb 2012 10:48:57 GMT

Thanks very much Celambarasan!

Exactly what i needed!

Nick