topic Re: Indirect Set Analysis w/ Prev/Next ID in QlikView

Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 13:50:30 GMT

Hi, I am trying to get the previous or next value within a group. While the results are correct in aggregate, the #s are not associating with the expected record. For each group, the value in "Next Value" should be associated with the previous row when using this expression SUM({$<id = P(id_next)>} value) . What am I missing (see attached for Load Script)?

EDIT: Though the example uses a table, the application does not (i.e: use of After/Before & Above/Below is not suitable).

Re: Indirect Set Analysis w/ Prev/Next ID

Mon, 05 Oct 2015 14:33:11 GMT

Hi Peter,

not sure I understand the exact output you are trying to achieve, but if you are looking at something similar to above but for the script, take a look at the previous or peek functions to get you on the right track in changing your script.

if you can expand a bit more on your expected output, I'll be able to help further

Hope that helps

Joe

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 14:35:41 GMT

Every value in the Next Value column in the QVW should be 1 row higher than it actually is. I'm trying to have a row dynamically (not in Load Script) get the Value of the next row.

Re: Indirect Set Analysis w/ Prev/Next ID

Mon, 05 Oct 2015 15:52:27 GMT

Hi Peter,

you've said in your post this isn't going to be used in a chart, so previous etc isn't an option.

How is this actually going to be used in practice?

Are you just looking for an expression? If so how is this going to be used if not within a chart? Will it be within a set text box of some kind?

Basically some detail around your question would be helpful

thanks

Joe

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 16:01:51 GMT

I said it won't be in a table. It will be in a chart.

Yes, I am looking for an expression, specifically a correction to the one in my original post. Within each GROUP_ID, for a given ID, I need to access the child records where ID = ID_NEXT (would be done with a self-join in SQL). the child records for a given ID, when summed give VALUE. NEXT VALUE should be VALUE for where ID = ID_NEXT.

So the below is the table in the QVW. What Iw ant is for "143" to be on the 1st row. The expression I wrote should be doing that from what I can tell (It is saying: for ID = 1, get the SUM of VALUE where ID=ID_NEXT=2), but it is instead putting it on the row of ID_NEXT.

Re: Indirect Set Analysis w/ Prev/Next ID

Mon, 05 Oct 2015 16:35:44 GMT

Hi Peter,

you said table but then said you don't want to use previous/above etc, functions which you can't use in a table, given that it's actually a chart within your example (and you called it a table), I assumed that you simply got mixed up between the two that's all.

Your current expression

SUM({$<id = P(id_next)>} value)

won't work I'm afraid, as the modifier with set analysis is done across the total expression and not on a row by row basis. The example you highlighted is selecting the possible values of id_next for the whole set, not per ID.

If as you have said, you can't use previous/above or some other in chart function, then I would suggest looking at a script change as you final option (using one of the functions I highlighted earlier).

Hope that helps

Joe

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 17:12:05 GMT

BELOW(SUM(value)) gives what I am looking for if I only have group_id & id (in that order) as dimensions. However, the real solution needs to not care about the specifics of a table (it needs to work as a KPI and in a chart); you can see the total is NULL which is not correct.

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 17:37:30 GMT

Okay, so upon selecting individual IDs and using a text object w/ the original expression, I do get correct results, which led to the realization that the dimensions are the problem. I cannot simply use TOTAL to ignore the dimensions, as that gives the SUM of all entries in the same group. Is there no way to "step" outside the dimension you're in for an expression? I guess I'm wishing for "Dimension Analysis", since I was treating Set Analysis as if it could forcibly set Dimension.

Re: Indirect Set Analysis w/ Prev/Next ID

martin_dideriks — Mon, 05 Oct 2015 18:11:54 GMT

Try this:

Sum(

Aggr(

Below(sum({$<id>} value ),1 )

,id)

)

Hope this helps and gives what you are asking for.

//Martin

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 18:17:01 GMT

That expression spans group_id, which is where there are control breaks in this logic. Additionally, your solution is sensitive to the order and does not sue next_id to actually find the proper record.

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Mon, 05 Oct 2015 18:18:20 GMT

It's looking like i'll have to refactor the data model with a bridge table between parent & children (to avoid duplicating the child data). that's really inelegant, though...

Re: Indirect Set Analysis w/ Prev/Next ID

martin_dideriks — Mon, 05 Oct 2015 18:35:22 GMT

As per my understanding, you wanted row with ID 1 to show the value for ID 2, ID 2 should show the value for ID 3 etc?

If that is the case, please have another look at the expression or the attached file.

The expression does exactly that - it does not need a specific sortorder.

Every value in the Next Value column in the QVW should be 1 row higher than it actually is. I'm trying to have a row dynamically (not in Load Script) get the Value of the next row.

So the below is the table in the QVW. What Iw ant is for "143" to be on the 1st row.

If that is not correct, I dont really understand your requirement.

//Martin

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Tue, 06 Oct 2015 11:16:29 GMT

Sorry for being imprecise. I did not distinguish between Table Box & Straight/Picot Table Charts in what I said.

Thank you for your assistance.

Re: Indirect Set Analysis w/ Prev/Next ID

karimeddini_sla — Tue, 06 Oct 2015 11:18:57 GMT

That statement wasn't absolutely every aspect of my requirements. It doesn't override what I said before then; it was only to help explain, though I can understand why that might not have been clear. Thank you for your effort.