Joining two Transaction lists into one Best Practices

Question

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

AhmedBennis · Accepted Answer

Hi Sandy,You're right. Pigment doesn’t allow you to directly combine two transaction lists within the tool.I would recommandoption 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

KBO · Answer

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 Refunique 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!

Ask your question here!

Not finding what you're looking for?

Sign up

Single Sign-On Need help?

Log into the Pigment Community

Single Sign-On Need help?

Scanning file for viruses.

This file cannot be downloaded