beginner question : V-lookup problem

4.45K viewsFormulas and Functions
0

Hi ,

I am a beginner to Quantrix.
I’ve read many lookup function like select / using as in the website.
However, when I was trying to do something like excel v-lookup. I have no idea at all :
Matrix 1 with category Colour :
Red 1
Blue 2
Green 3
Yellow 4

Matrix 2 with lookup category:
Blue x
Green x

I type using as / select formula and try to get blue x = 2 and green x = 3; however, both of them do not work.

Anyone could give advice me on this ?

0

Hello Modeller,

Awesome !

Thanks

Regards
David

0

Hello David,

You can use the structural function @item() within the select function to create your key and lookup values as shown below:

Lookup result = select(Source2::Value list, @item(Source2::Key list), @item(Lookup value))

I believe this is what you are looking for as it eliminates the need for hidden keys. See the attached model.

0

Hi James,

Yes, this is also what I did for look up.
I am curious why we need to do that in order to look up something.

Regards,
David

0

To elaborate on my last post – you can collapse the columns in the table view and then select ‘view’>’hide collapsed items’ to get the result shown in the attached model above.

Hope that helps.

James

0

Hi,

You can use @item(Category) to construct the key/lookup values, then just hide these in the final table.

Does this solve your problem?

Regards,
James

0

Thank you for your reply, Jlin.

I was thinking the lookup value in the category area. Please find the attached file.

Thanks !

0

It’s best if you provide a sample model or at least your actual formulas. Have you looked at the Online Documentation and Sample Models? They are not great, but there is enough information to get you started.

To answer your question, please see the example model. Is this what you’re looking for?

0

I tried adding one more column copying the name of the category to item. and it works.

Why we need to duplicate the category to items in order to make select / using as function works ?