Why do I need a Helper when I know the Item?
Hi there,
Quantrix is logical und structured, and a cool thing. But there is one thing I do not understand, and I’m crossing the same thing in a lot of places, when doing SELECT statements, or in other places. I’m still not sure if my brain is twisted or if it is Quantrix 🙂
I compiled a very simple example to illustrate the point.
In a 3-dim matrix, I want to select one index on one dimension, sum over the second dimension, and do it for all elements in the third dimension.
Easy, as I know the item I want to slice – “S1” – by name.
I do not find a way to do this … not using the item name, not using the descriptor. Everybody is doing this using a helper. Why do I need to convert the address to content, and then search the cell content to get back the address of the cell?? This looks very inefficient to me.
Is there anybody out there who can fix my mind? Or show me how to solve the problem without a helper column (and yes, I know how I can do it if both matricies have the same structure and linked categories, I could sum over all “Sektor”-items, but that’s not what I want to do …
Thanks for your help, Gilbert
Here I post the updated sample model, containing all the solutions. Thanks again to “S A U” and Lyndsey!
Happy modeling everybody!
Gilbert
Dear SAU,
I’m so sorry!!
For an unknown reason the “:” went away in my formulas, so I wrote “=” instead of “:=”, seems I was too tired last night!
Now, both formulas work!!
Thanks for all your explanations, I think these are very important techniques for me, correctly addressing items in other matrices based on items.
I’ve never seen this kind of stuff somewhere else, so this is extremly helpfull.
you definitvely won the prize!
Gilbert
Gilbert, let’s explicitly specify the third parameter of select() function (#true#):
*
*
*
Hope the formulas will be workable in this case.
Good luck
Gilbert, both formulas are working, as indicated by green check marks in upper left corners of the pictures.
Good luck
Very interesting! Your formula seems somehow to do what I want: test items from the source-category against items or literals on the target matrices.
Unfortunately, all I get again is “Syntax error”. I tried to play around, but could not make the formula work.
does it run on your machine? I’m on Mac 22.1.2 …
Thanks for your help!
Gilbert, try another formula version, plz:
*
*
It looks like a Mac version of QM doesn’t recognize such formula syntax’s.
These two formulas are complete equivalents. The first one is just shorter.
1. The @Data::Sektor:(Years):(Linien) expression virtually propagates the item names of the Sektor category as many times as the Years and Linien categories have items: size(#Years) * size(#Linien) = 7 * 7 = 49 times in your case).
2. Function slice() is undocumented, it may be replaced by list() function. It assign only one Jahre category item name to the current cell rather then a list its names.
Also note, you can explicitly specify the literal (“S2“, for example) in the formula, this is that you requested in your post above.
Good luck
Dear SAU,
Thank you very much for suggestion. Looks really cool!!
Unfortunately, it gives me just “Syntax error”, without telling me why.
Some questions:
* What does mean to put a category in parathesis (e.g. (Years) )?
* why do you use “slice” – this function was a feature request I suggest to actually solve the problem, but in my universe it is not available.
Very curious … Gilbert
Thank you very much Lyndsey for your suggestion. This one works perfectly! So, you get the prize for the first attempt.
I admit that while building a simplified model to illustrate my case, I made it too simple.
In the real case, I tried also “SELECT” and “USING AS” in vane. Maybe I’m still a bit too confused about this three variants – with sumif as my third and last option. Sometimes, I find a solution, but there are often cases where I don’t. With neither of the three. What I’m trying to do is to advocate for these “unsolvable” cases …
So, updated example, same game. I changed the following to things, which I think nor “USING AS”, nor “SELECT” can cope with:
- Instead of using the category “X” with the item “S1”, I want to use directly a literal “S1” (this was the case in my real example, though I agree that this is debatable if using a literal is bad style anyway and should not be possible)
- The Years category are not linked (In my real example, I have two different time scales, the one when energy is sold and the one when it’s produced, so I need to match years which are on a different category).
Ok, challenge for everyone: is the problem solvable without the helper matrix also this time?
Gilbert
P.S.: The solution I have in mind, as functions which I feel are needed to solve this kind of cases, and make life much easier:
a) slice(matrix,item1)Â = slices out from matrix on item1, returns a matrix with dim n-1
b) doalong(matrix, category, function)  => takes a matrix, and applies the function (sum, average, …) along the category, returns a matrix with dim n-1
Hi Gilbert, try this maybe? I could be misunderstanding your goal, but I don’t know that sumif() is needed…I’ve done this with a “using as” but could also be done with a script. Let me know if it helps? 
Ooops, not a script, I meant with a select statement.
Dear SAU,
Thank you very much for suggestion. Looks really cool!!
Unfortunately, it gives me just “Syntax error”, without telling me why.
Some questions:
* What does mean to put a category in parathesis (e.g. (Years) )?
* why do you use “slice” – this function was a feature request I suggest to actually solve the problem, but in my universe it is not available.
Very curious … Gilbert