topic Re: Determine which expression column number has a particular value in App Development

Determine which expression column number has a particular value

qlikviewrmw — Fri, 02 Nov 2018 11:08:48 GMT

Good day

I am trying to determine from a PivotTable which expression has a particular value associated with it.

What I am wanting is to find out which column in the expressions has a ZERO value. The first occurence of the ZERO.

And then display this in my dimension. My expression totals are based on year and week so trying to figure out which year and week has the first occurence of a ZERO.

Is this even possible?

Thank you in advance

Re: Determine which expression column number has a particular value

sunny_talwar — Fri, 02 Nov 2018 11:16:13 GMT

Would you be able to show a sample of what you have? I think seeing what you have will help us understand what you might need

Re: Determine which expression column number has a particular value

qlikviewrmw — Fri, 02 Nov 2018 11:35:07 GMT

A rough outline. There is the parent group dimension. Then a grouping for expressions for year and week showing a value.

When a zero is returned I want to know which column it was from, or for that matter what year and week. And then display that in the "red shaded" column. Hope that makes sense and helps in assisting?

Re: Determine which expression column number has a particular value

sunny_talwar — Fri, 02 Nov 2018 11:49:40 GMT

Something like this

First 0 is a calculated dimension like this

=Aggr(Min(Aggr(If(Sum(Amount) = 0, RowNo()), PG, Year, Week)), PG)

Re: Determine which expression column number has a particular value

qlikviewrmw — Fri, 02 Nov 2018 11:59:36 GMT

Thank you, going to give it a try. Much appreciated!!

Re: Determine which expression column number has a particular value

qlikviewrmw — Fri, 02 Nov 2018 12:03:56 GMT

Seems to work for some rows and then others it does not. Very strange. I will investigate and try and few different things to see if I can get this to work.

Re: Determine which expression column number has a particular value

sunny_talwar — Fri, 02 Nov 2018 12:06:36 GMT

Are these the only dimensions in your chart or do you have more dimensions? Also, when you say it doesn't work... do you mean that the value isn't correct or just gives null or 0?

Re: Determine which expression column number has a particular value

sunny_talwar — Fri, 02 Nov 2018 12:07:43 GMT

Since you are using Qlik Sense, try this

=Aggr(Min(Aggr(If(Sum(Amount) = 0, RowNo()), PG, (Year, (NUMERIC)), (Week, (NUMERIC)))), PG)

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 10:39:02 GMT

Apologies for late reply.

When I say it does not work I mean that for some of the rows it returns the correct column that has a ZERO. Other times it returns a column that does not match, and other times it returns a - which is nothing. I have tried with your above code as well and that does not work at all, get - for every row

Re: Determine which expression column number has a particular value

sunny_talwar — Mon, 05 Nov 2018 12:05:52 GMT

Did you get to try this?

=Aggr(Min(Aggr(If(Sum(Amount) = 0, RowNo()), PG, (Year, (NUMERIC)), (Week, (NUMERIC)))), PG)

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 12:23:10 GMT

Yes I did but it does not appear to work. The comma after the Year is underlined in red, so seems something is missing and or not correct.

Re: Determine which expression column number has a particular value

sunny_talwar — Mon, 05 Nov 2018 12:27:36 GMT

You are using this in Qlik Sense, right?

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 13:02:30 GMT

Yes I am

Re: Determine which expression column number has a particular value

sunny_talwar — Mon, 05 Nov 2018 13:17:04 GMT

Did you try to click okay to see if it gives any output or not? Sometimes syntax editor doesn't recognize a particular syntax, but the syntax isn't wrong. If you have not tried that yet, can you give it a shot... because the above expression does work for me.

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 13:28:09 GMT

Just gives a -, no error or anything

Re: Determine which expression column number has a particular value

sunny_talwar — Mon, 05 Nov 2018 13:32:24 GMT

Would you be able to post a screenshot of the expression you have used which also show the error after Year that you are talking about?

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 13:47:42 GMT

As you can see after the comma after Year, it is underlined in red and then the rest is not recognized.

Re: Determine which expression column number has a particular value

sunny_talwar — Mon, 05 Nov 2018 13:50:44 GMT

Two things

1) It seems like a screenshot from QlikView, whereas you have mentioned that you are using Qlik Sense. If you are using QlikView, this will only work if you have QV12 or above.

2) I don't think this is the issue as I am sure you are using QlikView 11.2 or something, but just to make sure do you really have a field called Year or is it called something else? Make sure you use the correct field names.

Best,

Sunny

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 14:05:25 GMT

1) My apologies. I am still quite new to Qlikview, self taught, and was under the impression Qlik Sense was the code used with in Qlikview. It is a Qlikview report, and trying to do this in a pivottable calculated dimension.

2) My Qlikview is version 10.

It appears then that what is wanted by management is not possible due to the limitations of the version of Qlikview I am making use of.

Re: Determine which expression column number has a particular value

qlikviewrmw — Mon, 05 Nov 2018 14:06:18 GMT

My fields are exactly as they have been mentioned. Year, Week, Amount, PG.