Skip to main content
Solved

Matching 2 lists with ranges

  • 22 July 2024
  • 5 replies
  • 64 views

I have two lists.

  1. List with account numbers
  2. List with account groups and min + max ranges

I want a function that assigns the numbers from the first list to the correct category in the second list if the number is within the respective range.

I found the solution in Excel and could not find a suitable way to implement this in Pigment. Here is the Excel solution:

=INDEX($D$2:$D$6, MATCH(TRUE, ($E$2:$E$6 <= A2) * ($F$2:$F$6 >= A2), 0))
  • INDEX($D$2:$D$6): This part selects the Category column.
  • MATCH(TRUE, ($E$2:$E$6 <= A2) * ($F$2:$F$6 >= A2), 0): This part finds the row where the number in column A falls within the Min and Max range.

I tried to use the MATCH function with some IF clauses, but that was not possible. Also the IN function could help with ranges, but I haven’t found a way to combine it with the MATCH function.

Is there any way to realize this with Pigment? If not, maybe that could be helpful in the future.

5 replies

francois
Rank
Userlevel 7
Badge +14

Hi Marco,

Have you looked into this article already?
 

 

If that does not help, could you share a basic example of the dataset, anonymised of course?

François

François (Pigment)
MARCO_BMG
Rank
Badge +1

Hi Francois,

thanks, that really helped me.

I created a Cluster that worked fine for me, but now I want to extract only the exact group per account and want to ignore the rest.

This is how my cluster looks (Account numbers matched with Account groups):

I tried to use this formula, but it did not give me the groups, but only the account numbers:

‘Account_List.Account_Number’[BY: → ‘Account Cluster’]

My final goal is to add the ‘Account Group’ info from the cluster to the ‘Account_List’ for every Account item.

M X BMG
CDALMAY
Userlevel 4
Badge +7

Hi @MARCO_BMG,

Assuming you have Account Group in Columns and Accounts List in Rows in your screenshot-ed metric, you basically want to remove the Account Group from your metric ?

The formula you need to put on your Account Group property in the Account List dimension, should be :

'Account Cluster'[REMOVE LASTNONBLANK: 'Account Group']

Let me know if that helps,

Clement
MARCO_BMG
Rank
Badge +1

Hi Clement,

We used REMOVE FIRSTNONBLANK and it worked as well. Is there a difference or will both work in this case?

Thanks for your help!

M X BMG
CDALMAY
Userlevel 4
Badge +7

Hi @MARCO_BMG,

In this case both will work as there is only one ‘Account Group’ for each Account list, so first or last will be the same once we remove all BLANKS.

Glad it help, 

Clement

Reply


Ask your question here!

Terms & ConditionsCookie settings