Solved

Joining 2 transaction lists into one

  • 9 August 2023
  • 5 replies
  • 155 views

Userlevel 2
Badge +2

Hi Community:

 

The problem: I have 2 transaction list with identical fields. I want to join them together in one single transaction list.

How to do it?

icon

Best answer by Khalid Awale 10 August 2023, 17:41

View original

5 replies

Userlevel 2
Badge

Hello Guillermo,

 

When it comes to merging two transaction lists that share identical properties, a straightforward approach involves exporting one of the lists as a CSV, as illustrated below:

 

This exported CSV can now be seamlessly integrated into the other transaction list, as shown below:

 

 

 

Since the fields are identical, you can select ‘Autofill source fields’ without needing to make any changes once you reach the Properties mapping stage of the import:

 

 

Now, both transaction lists are compiled. Keep in mind that any duplicate rows in the separate lists will only show up once. The list will now look like the original transaction list + any unique rows in the other transaction list.

 

Please let me know if this works for you, or if you were looking to keep the two transaction lists separate. 

Thanks!

Userlevel 2
Badge +2

Thanks Khalid, it is a good idea, not sure if applicable for +1M lines list.

 

Userlevel 2
Badge +3

Hi All, 

Jumping on this thread -- I’m also working with a large set of data and am curious to know what’s the most efficient way to combine two transaction lists. Ideally, it would be great to not have to manually export one list and upload to the other since the data is set up to a connector.

In other words, what would be the best approach if we were to keep the lists separate? 

Userlevel 6
Badge +12

Hi,

What Khalid described, exporting + importing, seems to be the best way if you have a unique property for each row.

If you don’t have a unique property, then it would create duplicates. (Making the merge job more complex)

 

You can try on a copy first and if you’re satisfied, replicate on production.

 

About Guillermo’s remark:

 not sure if applicable for +1M lines list.

I believe 1M rows is not an issue. It can take a few seconds/minutes but Pigment can handle it.

You can try and come back here or go to Support shall you face any issue.

 

About Kyla’s question

it would be great to not have to manually export one list and upload to the other since the data is set up to a connector.

In other words, what would be the best approach if we were to keep the lists separate? 

If your data is set up with connectors, please note that you can have multiple connectors imports set up in one list. So you can recreate your “secondary” import in your “primary” import. 

If you want to keep the two lists separated, in means you’ll have to run again the process each time one list gets new data? Seems not very handy.

My recommendation would be to have only one list, with two import pipelines.

 

We’re also working on a new feature to be able to import from Pigment blocks to Pigment blocks, maybe that would help the case in the future.

 

Hope this helps. :) 

Userlevel 2
Badge +3

To add to Guillermo’s remark on large Transaction Lists, I’ve also had difficulty needing to append large +1M row datasets. I believe this has been recorded in the community before that the 200MB flat file limit means I’ve needed to export, split and then import multiple files in succession (and track to make sure I’m not uploading the same split file twice). Even if we maintained a size limit per file, having some option to bulk import multiple flat files simultaneously would help a lot!

For the new feature which we would be able to import from Pigment block to Pigment block, would this enable a kind of “union these two blocks with the same properties” use case described in the question above? Given that we’re already able to have load dates added to a connector, seems like it’d be really useful to take a collection of lists and combine them for historization (e.g. Page filter on based on Load Dates). For example, if I want to implement a delta load design in Pigment, it would be fantastic if I could have some base Transaction List which I could add to incrementally without needing to export/import as flat files.

Reply