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 :)
Best answer by Keiran
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
+11Hi 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!
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 ?
+11Hi 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)
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.
+11Hi Alexandre,
If you raise a ticket (Submit a request – Pigment) with a link to the Block, I’ll take a look.
+11Just 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
+1Hi 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?
+13I think you could just chain properties? Like Transaction.'Account and Entity'.Report
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
