Set Expression using formula

keithlawrence — Thu, 19 Apr 2018 09:27:08 GMT

I'm struggling with this for quite a while now and after searching the Community I've come to the conclusion it's not a situation that's been asked before.

I have a list of temperatures in a month and a field flag. I need to be a pull through the flag for the lowest temp in each month. No matter what I try it seems to only pull through the flag on the lowest temp for the entire data rather than by each individual month. My basic data is as such:

LOAD * INLINE [

Month, Temp, Flag, Country

1, 10, 1, AA

1, 11, 0, AA

1, 15, 0, BB

2, 11, 0, CC

2, 15, 1, CC

3, 10, 0, AA

3, 9, 1, BB

3, 15, 0, BB

4, 16, 0, CC

4, 9, 0, CC

4, 10, 1, AA

];

I can get the min temp per month by simply using Min(Temp). What I need is to know the flag for that min temp per month. I've tried the following with no success:

only({<Temp={$(=min(Temp))}>}Flag)

only({<Temp={'$(=min(Temp))'}>}Flag)

only({<Temp={'$(=Aggr(min(Temp)),Month)'}>}Flag)

Aggr(only({<Temp={'$(=min(Temp))'}>}Flag),Month)

I basically get the flag working on month 3 and 4 as they are both min temp 9 and that is the overall min temp.

How do I get it to work for the individual months?

My next problem is establishing what country had that temp but if I can't fix the above, I can't get the country.

Any ideas?

Re: Set Expression using formula

YoussefBelloum — Thu, 19 Apr 2018 09:32:33 GMT

Hi,

can you just simply describe what is the expected output with the data shown above ? via an image for example

Re: Set Expression using formula

MK_QSL — Thu, 19 Apr 2018 09:33:51 GMT

FirstSortedValue(DISTINCT Flag,Temp)

Don't forget to untick suppress Zero values from Presentation tab.

Re: Set Expression using formula

keithlawrence — Thu, 19 Apr 2018 09:40:45 GMT

It's quite annoying that it was as simple as using a FirstSortedValue!! It works perfectly. I really should have picked that up

topic Re: Set Expression using formula in QlikView

Set Expression using formula

Re: Set Expression using formula

Re: Set Expression using formula

Re: Set Expression using formula