topic Re: Conditional Minimum in QlikView

Conditional Minimum

bhaskarsharma03 — Thu, 12 Sep 2013 06:46:04 GMT

Hi,

I need to find minimum from a list after excluding everything <=0, blanks, spaces and NULL values. I have attached two files to explain the requirement. Any help will be very much appreciated.

Thanks

Re: Conditional Minimum

Sokkorn — Thu, 12 Sep 2013 07:16:27 GMT

Hi Bhaskar,

I would suggest to add new field for easy control in expression.

FindMinValue:

LOAD *,If(Values<=0 or Len(Values)<1,0,1) AS [Cond];

LOAD * INLINE [

Region, Values

East, -1

East, 0

East

East, 5

North, 23

North, 7

North, 0

North, 25

North, 7

South, 9

West, 55

West, 23

North-East,

North-East, 0

];

After load data, let try create straight table with:

1. Dimension: Region

2. Expression: Min({$<Cond = {1}>}Values)

See sample attached file

Regards,

Sokkorn

Re: Conditional Minimum

Anonymous — Thu, 12 Sep 2013 07:26:55 GMT

better to use directly the inline table and a set analysis for the min values

Min({$ <Values={">0"}>} Values)

For the North-East where the result is NULL, you can work with the Presentation and the Suppress Zero-Values and the Null Symbol

Re: Conditional Minimum

Thu, 12 Sep 2013 07:29:48 GMT

Hi,

Try like this in script.

MinValue:

LOAD Region as Reg,

Min(Values) as MinVal

Group by Region;

LOAD Region, Values

Resident FindMinValue

where len(Trim(Values))>0 and Values <> 0 and not IsNull(Values);

Hope it works.