Skip to main content
Answer

Joining two Transaction lists into one Best Practices

  • November 10, 2025
  • 2 replies
  • 97 views
Sandy B
Apprentice Author
Forum|alt.badge.img

Hello,

I have 2 different transaction lists from 2 separate sources. I would like to combinate both Transaction lists thanks to a common textual field “Reference Number”. On SQL, I would use an “Union” operator. 

What about Pigment? If I guess correctly, I cannot combine Transaction lists direclty on Pigment. But I have 3 options:
    1. Transform my SQL request to have an unique source and Transaction list.
    2. Manually export, transform and load a third transaction list (Too time-consuming).
    3. Use my “Reference Number” field as a dimension.

This reference dimension should contain thousands, even millions of elements. Then, is it wise to use it as a pivot dimension? 

Thanks for helping me ou :)
Best, Sandy

Best answer by AhmedBennis

Hi Sandy,

You're right. Pigment doesn’t allow you to directly combine two transaction lists within the tool.


I would recommand option 1. Combine both datasets at the source level before loading them into Pigment. That ensures you have a single, clean (and consistent!) transaction list and avoids performance issues.
Option 2 is doable but not scalable and I would not recommand option 3.

If option 1 isn’t possible, don’t forget you can also do List-to-List imports to feed a single Transaction list from multiple sources.

Ahmed
 

2 replies

AhmedBennis
Employee
Forum|alt.badge.img+1
  • AhmedBennisEmployee
  • Employee
  • Answer
  • November 12, 2025

Hi Sandy,

You're right. Pigment doesn’t allow you to directly combine two transaction lists within the tool.


I would recommand option 1. Combine both datasets at the source level before loading them into Pigment. That ensures you have a single, clean (and consistent!) transaction list and avoids performance issues.
Option 2 is doable but not scalable and I would not recommand option 3.

If option 1 isn’t possible, don’t forget you can also do List-to-List imports to feed a single Transaction list from multiple sources.

Ahmed
 

Isabella Leslie-Miller
K
Apprentice Helper
Forum|alt.badge.img+4
  • KBOApprentice Helper
  • Apprentice Helper
  • November 12, 2025

Hey Ahmed 👋

Thank you for your answer!

The list-to-list imports only work when there’s a unique key (Code), which isn’t the case on our side

(see the capture below — the Ref unique is repeated).

Transaction list 1 :

 

Transaction list 2 : 

 

The issue is that if we try to generate a unique key within Pigment (using a RANK or a concatenation),

we end up losing some transaction lines during the import.

What would you recommend to ensure uniqueness without losing data?

Or do you see a cleaner approach within Pigment that I might have missed ?

Thanks! 

Karim Bouali

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement