topic Re: Set analysis returning null value for entire table when records don't meet the defined set criteria in QlikView

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

Dayna — Mon, 16 Jun 2014 11:45:17 GMT

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

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

Mon, 16 Jun 2014 11:53:12 GMT

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

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

Dayna — Mon, 16 Jun 2014 11:56:01 GMT

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

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

Mon, 16 Jun 2014 12:09:00 GMT

Dayna

what is the value for

pt_um = {$(=(um_um))}

i don't have it in the qvw

chris

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

Dayna — Mon, 16 Jun 2014 12:46:34 GMT

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

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

Mon, 16 Jun 2014 13:00:25 GMT

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

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

Dayna — Mon, 16 Jun 2014 13:13:44 GMT

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

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

Mon, 16 Jun 2014 13:57:13 GMT

Dayna

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

best regards

Chris

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

Dayna — Tue, 17 Jun 2014 12:09:00 GMT

Thanks Chris!