Solved

Try to do the equivalent of vlookup

  • 27 September 2022
  • 9 replies
  • 717 views

Userlevel 3
Badge +9

Hello,

I want to use a vlookup on a non-dimension column. Is it possible ?

Exemple : 

Dimension A : I have those 2 columns : I want to retrieve the Manager via the e-mail

Dimension B : I have my e-mails and my managers.

 

Question : How can I get my managers in Dimension A ?

Thank you :)

icon

Best answer by Keiran 29 September 2022, 11:26

View original

9 replies

Userlevel 5
Badge +8

Hi Alexandre,

 

In this case, say you have your Employees Dimension, with email as a property

Then your Emails Dimension with ‘Employee Name’ as a Dimension Property

You can use the ITEM() function to bring the names of the employees back, using their emails

There’s a better write up of the ITEM() function here: ITEM function | Community (gopigment.com)

 

I hope this helps, but let me know if there’s any more questions!

Userlevel 3
Badge +9

Hello,

That is not what I want because I don’t have Email dimension and I don’t want to have one.

There is no possibility to do it without Email dimension ?

Userlevel 5
Badge +8

Hi Alexandre,

 

You just need to refer to the dimension property you want to lookup, it dosen’t have to be a dedicated dimension

ITEM(Dimension.PropertyYouWantToLookup , ReturnDimension.PropertyYouWantToReturn)

Userlevel 3
Badge +9

I tried it but I have this error message : Error: Dimension error: formula Dimensions and current Block Dimensions are not compatible. Can't implicitly remove Employees Dimension(s) on non numerical Metric.

Userlevel 5
Badge +8

Hi Alexandre,

 

If you raise a ticket (Submit a request – Pigment) with a link to the Block, I’ll take a look.

Userlevel 5
Badge +8

Just updating following the ticket, the solution was;

ITEM(

IF(Employee.Work_Email = 'Other_List'.Persons_Email, Employees.'Employee #' )

[REMOVE FIRSTNONBLANK : Employee] , Employee)

This works by finding the matching email address, returning their employee ID and then using the ITEM() function to find the relevant Dimension to return, rather than a text value.

 

We’ve used the following functions here;

ITEM function | Community (gopigment.com)

IF function | Community (gopigment.com)

REMOVE modifier | Community (gopigment.com)

This guide goes through FIRSTNONBLANK

Get the first / last time a value appears in a metric: formula building guide | Community (gopigment.com)

Userlevel 2
Badge +2

Hi community,

 

It is a simple thing. I’m struggling with a lookup type formula.

(value to lookup, where to lookup, value to return)

I’m trying to populate a column ‘Report’ (property type dimension) in a Transaction List

In the same Transaction List I have the lookup field (property type dimension)

In ‘Account and Entity’ Dimension list, I have The lookup fieId ‘Concat’ (unique field, property text).

In the same dimension list I have the return field ‘Report’ (property dimension).

 

‘Report’ is a dimension property and it is linked to its own dimension list ‘Report 2’.

 

I iterated several times with item formula based on the above advice without success.

 

What is the logic to replicate a lookup as described above?

Userlevel 6
Badge +14

I think you could just chain properties? Like Transaction.'Account and Entity'.Report

Userlevel 2
Badge +2

It was as simple as that! 

Thanks a lot François :)

Reply