Solved

Try to do the equivalent of vlookup

  • 27 September 2022
  • 6 replies
  • 122 views

Badge +2

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

6 replies

Userlevel 3
Badge +2

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!

Badge +2

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 3
Badge +2

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)

Badge +2

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 3
Badge +2

Hi Alexandre,

 

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

Userlevel 3
Badge +2

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)

Reply