*** Equivalent to VLookup in Excel (Returning a String Value) by Using Expression *** (Simple Question)

[IAMDV]

Dear All,

I have a simple question and I had already searched on the forum without any luck. I hope someone can help me on this one.

Lets say we have table...

Product Sales

A 10

B 12

C 15

If I wanted to refer to the product with maximum sales i,e. 'C' in this example by using an expression (with and without set analysis). This is equivalent to VLookup on Max(Sales) in Excel.

TIA!

Cheers - DV

[matt_crowther]

DV,

Look at the function: firstsortedvalue() - it works for me.

All the best,

Matt - Visual Analytics Ltd

[#3]

Hello,

I suggest you to add a variable

Max= Max(Sales)

and use the following expression

if

(aggr(Sales,Product)=Max,Product)

regards,

Beyrem

[IAMDV]

Beyrem - Please can you share the QV document as example?

Thanks again for your time.

Cheers - DV

[IAMDV]

Matt - Thanks for quick response. Please can you show an example QV document. I had tried the firstsortedvalue() and it is returning an empty value.

Thanks in advance.

Cheers - DV

[#6]

Hi Deepak,

Have you considered that your vlookup equivalent in QV is an applymap in the script?

I know I'm asking the obvious, but it doesn't hurt.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Try

Only({< Sales = {$(=Max(Sales))} >} Sales)

If you only want to know the product, then

=FirstSortedValue(Product, -Sales)

Should be enough (not the minus sign before Sales, meaning "descending").

Hope that helps.

[IAMDV]

Miguel - Thanks so much! I assume that you mean...

=Only({< Sales = {$(=Max(Sales))} >} Product)

instead of

=Only({< Sales = {$(=Max(Sales))} >} Sales)

Thanks to everyone!

Cheers - DV

[IAMDV]

Cheenu - Thank you for replying back. Please can you eloborate more on the ApplyMap? I am newbie with QV...

Cheers - DV

[#10]

Hi Deepak,

an applymap is a vlookup in the script, whereby you are trying to return one value from a table into another where it doesn't exist and you need it.

Your mapping load statement (a transient table that will not be loaded as an independent table, hence you will not be able to see it on TableViewer, i.e. CTRL+T in front-end) will look like:

Mapping_Table:

Mapping load

LinkingField,

FieldYouWantToCopy

From SourceTable;

The rules for a mapping load are: (i) impervious of the source file, the mapping load statement should only contain 2 fields, the linking field through which you want to make the match and the field you want to import into another table (hence even if at source you have N-fields/columns, you can still call for it as a mapping load statement by just including the 2 relevant fields in the mapping load statement); (ii) the linking field must be the first field name called for in the mapping load statement.

Then, when you load your table and you want to call for that FieldYouWantToCopy from the mapping load:

Table1:

Load

X,

Y,

Z,

LinkingField,

applymap('Mapping_Table', LinkingField, 0) as FieldYouWantToCopy

From OtherTable;

What this does is look in Table1, find a match of LinkingField in the mapping table and return the associated FieldYouWantToCopy.

This technique allows you to call for an external field to a table DURING the load process.

The only other way around this would be to load Table1, then load the mappingload table as a normal table and do a left join to Table1, then reload Table1 and do some transformations using the FieldYouWantToCopy field.

Practical Case example:

ProductCost_Map:

MAPPING LOAD

ProductID,

ProductCost

From Products;

Sales_Table:

etc., etc.,

ProductID,

QuantitySold

applymap('ProductCost_Map', ProductID, 0) * Quantity as SalesRevenue

From SalesTable;

I hope this makes sense. If not, share your script (table from which you want to do the vlooukup and table on which you want to attach the vlookup value) and I'll give you the example.

Further info, in the applymap statement, the mapping table names must all be between 'single quote', the linking field name must exist in the load process (e.g. if in the above case example I have a script line such as "ID as ProductID", then the applymap 2nd parameter must be the source name, i.e ID and not the aliased name ProductID).

Secondly, always try and validate in the front-end that your applymap worked. E.g. If you make a typing mistake of mapping table name in the applymap, you won't get an error message at all, just null values being returned.

Lastly, quite naturally, since QV reads scripts top to bottom in tabs and tabs left to right, your mapping load table must exist PRIOR to using the applymap statement.

Let me know if you need more help on the scripting side.