topic Re: Expression -1 in QlikView

Expression -1

kevbrown — Thu, 09 Mar 2017 10:13:01 GMT

I have a table in excel that shows Name and another table that shows Name, Rank, Value. What I need to do is create a table in QV that shows Name, Rank, Value, Rank -1 (so if I'm rank 3, I need Rank 2), the Value and Name for that rank

For Value I think I need something like =if(Rank-1=Rank, Value)

For Name I think I need something like =if(Rank-1=Rank, Name) but I'm having no joy

Re: Expression -1

sunny_talwar — Thu, 09 Mar 2017 10:16:44 GMT

Would you be able to share some sample data with expected output from that data?

Re: Expression -1

Peter_Cammaert — Thu, 09 Mar 2017 11:15:52 GMT

Well, for one thing whenever the expression engine evaluates a comparison like "Rank -1 = Rank" it will always return false as there is no value for which this comparison returns true.

I guess that - given the nature of the associative model - it will be very difficult to make this work on the fly. You can easily create a correct table in your LOAD script. The best way to get an example is to follow Sunny's advice and post some source data.

Re: Expression -1

swuehl — Thu, 09 Mar 2017 11:53:14 GMT

Yes, context is important...

I may be wrong, but maybe it's enough to create a straight table chart with dimension Name and as expressions:

=Only(Value)

and

=Only(Rank)

Sort your table by Rank expression ascending.

Now you can use chart inter record functions like Above() to address the values in the line above the current:

=Above( Only(Value) )

=Above( Only(Name) )

Similar could be achieved in the script using inter record functions Previous() or Peek() in a sorted table load.