Connecting data from two transactions?

  • 24 August 2022
  • 1 reply

Userlevel 1
Badge +1
  • Enthusiastic Pigmenteer
  • 21 replies



I have a dimension “Self Served” that I get from an extract directly intro a transaction list A, in this transaction list  A I have another dimension “Client ID” that is shared with another transaction list B, and I added another column in transaction list B to get the dimension “Self Served” but I get an error, is there a way to connect both transaction lists with something similar to a vlook up in Excel? I dont want to keep importing or manually adding “Self Served” into the dimension “Client ID” so I can use the function ITEM. I am trying to automate the data so it comes directly from the extracts.



Thank you very much for your insights!





Best answer by francois 25 August 2022, 10:03

View original

1 reply

Userlevel 5
Badge +3

Hi Jose,
From my understanding here’s the flow of data:

  • List A, where you have both Client ID and Self Served
  • There should be a unique link between Client ID and Self Served, where a Client ID should have one Self Served property
  • List B, where you have Client ID and want to bring Self Served property, based on the information from Client ID of List B

Based on that, here’s what I would do

  • Create a property with dimension format Self Served in the dimension Client ID, with a formula ‘List A’.’Self-Served’[BY LASTNONBLANK: ‘List A’.’Client ID’]. You can try this formula in the formula playground first
  • Then you can just reference it in List B by calling ‘List B’.’Client ID’.’Self-Served’, calling the property ‘Self-Served’ you’ve just created in the dimension ‘Client ID’

Please note that I’ve used LASTNONBLANK because there may be several items in List A with the corresponding Client ID. Pigment needs a way to know which to take, so you can use LASTNONBLANK but also FIRSTNONBLANK or even FIRST or LAST. You can even also filter this by adding a FILTER like this ‘List A’.’Self-Served’[FILTER: ‘List A’.Period = ‘Current Period’][BY LASTNONBLANK: ‘List A’.’Client ID’] or any other kind of FILTER that could be based on a defined set of properties that would reduce the number of relevant lines in the List A.