Set analysis returning null value for entire table when records don't meet the defined set criteria

[Dayna]

Hello,

A bit of a hard one to explain but I'm hoping someone can help me.. I have an application where I want to convert the UoM of each product against a standard I have. The problem is, not every product has a conversion record against it (where this is the case, the UoM would be '1').

I believe I have the right expression to do the conversion:

({$<pt_um = {$(=(um_um))}, um_alt_um = {'MU'}>} um_conv) * sum(ld_qty_oh)

However, the entire table shows it as null. When I select a group of items that all have a um_conv against it, it shows me the values - but if any of the records don't meet the set criteria, it shows everything as null.

I've tried if statements where isnull(<set>) then show something else, but nothing has worked... I've attached my example application and the product to select is in group 3100 > 'RT20880X02' (as this shows how it should look).

If someone could advise how to get the set analysis to still return regardless of null values, it would be appreciated!

Kind Regards,

Dayna

[#2]

Hi Dayna

your problem doesn't come from set analysis but from min function which requires selections to be able to calculate value

best regards

Chris

[Dayna]

Chris,

I tried with a sum and all that does is change every value to '0' (unless I select one).

Any other ideas?

Kind Regards,

Dayna

[#4]

Dayna

what is the value for

pt_um = {$(=(um_um))}

i don't have it in the qvw

chris

[Dayna]

Chris,

It's there, it's to say that the UoM  for the product should equal the same in the UoM conversion table.

Kind Regards,

Dayna

[#6]

OK

sorry i went to fast

that will involve you need to select 1 value

other wise remove this pt_um = {$(=(um_um))}, from your set it will work

best regards

Chris

[Dayna]

Chris,

The um_um could be a different value to the pt_um, (so the conversion rate would be different) so I need to include something that knows the original rate.

Kind Regards,

Dayna

[#8]

Dayna

Here is a version where you have just to select um_um and you'll get the result

best regards

Chris

[Dayna]

Thanks Chris!