topic If RowNo with variable in QlikView

If RowNo with variable

alanmcgrath — Tue, 26 Apr 2016 15:27:34 GMT

I have a straight table in my application with a drill down group dimension. I have mapped in target values using a mapping load to my data set based on each of the dimensions in the group. I then have a variable that grabs the target value associated with whatever dimension is drilled in to. However, during the mapping load the Total targets don't come in since there is no "Total" in my dimension. I still need to display the total target on the total line so I am trying to use an IF statement to bring in the static total target if the row is 0 but I'm getting an error.

The variable works fine on its own in the expression but once I add the "If" it errors out saying its expecting a ')'. On the flip side if I use the If without the variable it also works fine. What am I missing??

The If I'm using is: =If(RowNo()=0, .87, $(vChgTarg_Org))

Re: If RowNo with variable

swuehl — Tue, 26 Apr 2016 17:03:04 GMT

And what is the exact definition of that variable?

Re: If RowNo with variable

alanmcgrath — Tue, 26 Apr 2016 17:10:42 GMT

=If(GetSelectedCount(OrgUnit1) >0 and GetSelectedCount(OrgUnit2)>0, ChgOrg3, If(GetSelectedCount(OrgUnit1)>0,ChgOrg2, If(GetSelectedCount(OrgUnit1)=0,ChgOrg1,'')))

My dimension group contains OrgUnit1, OrgUnit2, OrgUnit3, OrgUnit4 and so on, up to OrgUnit10. the ChgOrg# is the target associated with that level in the dimension. So ChgOrg3 will match up with OrgUnit3, ChgOrg2 will match up with OrgUnit2, etc.

This variable works fine in the expression but I obviously get a blank/Null on the total row. I want .87 to come in on the total row, but the If(RowNo expression doesn't seem to be working.

Thanks!

Re: If RowNo with variable

swuehl — Tue, 26 Apr 2016 17:16:38 GMT

Try

If(Dimensionality()=0, ...., ...)

Re: If RowNo with variable

alanmcgrath — Tue, 26 Apr 2016 17:30:45 GMT

Still get the same error - expecting ')' . but in the expression window is shows as expression ok. I guess it doesn't like the variable combined with the If. seems odd to me.

Re: If RowNo with variable

swuehl — Tue, 26 Apr 2016 17:40:43 GMT

Does the variable maybe expand to an expression including the leading equal sign?

Re: If RowNo with variable

alanmcgrath — Tue, 26 Apr 2016 17:50:32 GMT

It will show the text of the expression of the variable in the straight table if I remove the dollar expansion from the variable in the straight table's expression or if I remove the leading = from the variable definition. Otherwise the whole straight table shows blank with that error message.

Re: If RowNo with variable

swuehl — Tue, 26 Apr 2016 18:10:28 GMT

I believe your issue (with the error message) is caused by the way you've defined your variable and how you call the variable.

See

The Magic of Variables‌

Could you maybe post a small sample QVW to get a better view of your setting?

Re: If RowNo with variable

alanmcgrath — Wed, 27 Apr 2016 12:41:48 GMT

I added a sample QVW to the original message. The column "Chg Tgt" is where I'm using the expression. the table on the left shows that column working when I use just the variable and the column on the right shows it not working when I add the If(RowNo statement. I need to get .87 into the total line

Re: If RowNo with variable

swuehl — Wed, 27 Apr 2016 15:51:37 GMT

Maybe like this?

Re: If RowNo with variable

alanmcgrath — Wed, 27 Apr 2016 16:34:16 GMT

Yes that works perfectly. I never would have thought to use single quotes on the field in the variable.

Thanks!!!!