Skip to main content
Solved

Connecting data from two transactions?

  • August 24, 2022
  • 1 reply
  • 100 views

Forum|alt.badge.img+2
  • Seasoned Pigmenteer

Dears,

 

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!

 

BR,

Jose

Best answer by francois

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.

View original
Did this topic help you find an answer to your question?

francois
Employee
Forum|alt.badge.img+13
  • Employee
  • August 25, 2022

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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings