LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sassy1

Hello, I'm very new to Lavastorm so I could be doing this the hard way, but I've been tasked to join 10 files together having 2 common fields - date and telephone number but differing data for the rest of the file. So far, the only way I have successfully pared down the files to have unique rows is by using an Xref node. With this I get the upper output list where the 1st table had only these records, the middle output where the duplicates between the tables are now a single occurance, and the lower output where the 2nd table had rows exclusively in it. To gather this data together, I use the Cat node to combine the 3 outputs. In this way I have successfully joined both files and have eliminated duplicate records based on date and TN.

What I don't understand is if I put a duplicate node on this Cat node, it tells me I still have duplicates.

Another problem arises when you have to do this multiple times to 10 files. It's simple enough for 2 files, but joining 10 becomes a mess.

My question is, is there a simpler way to do this? I realize there is a duplicate node, but maybe don't understand how to use it in order to move forward in eliminating duplicates. I know the Sort node has a 'unique' option, but what I get out of that doesn't match what logically makes sense from what I do above to resolve duplicates.
Somewhere, I'm going wrong. I'd greatly appreciate any help you can give.

Thanks,
sassy1

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Hi sassy1,
I believe it is always the case that if you get duplicates on a join, it is because at least one of the inputs has a duplicate. In my experience has always been the case. I have found the duplicate detection node to be very useful in checking for duplicates in the final result and then for checking each of the inputs to identify the source of the duplicates. I have even left some duplicate detection nodes in production graphs to protect the next user of the graph from inadvertently doing something to cause duplicate records. I hope this helps.

The structure you describe, of using the X-Ref followed by a cat is the nicest way to do this. You can check every step and question each output as you join each of the inputs. I would do exactly this, joining 2 of each of the 10 together resulting in 5 outputs, then joining 2 of each of those (except for the extra one) resulting in 3, then joining 2 of the 3 resulting in 2, then joining those 2. Then normally I would check for dups in the result and leave it that way. Yes, this might look messy, but this is where I would highlight the nodes and choose Edit -> Group Selected Nodes to improve the BRE users view of all these nodes and make it look like one operation.

If later, I had a very large number of input records and was at a point where I needed to optimize the graph, I would replace the X-Ref/cat nodes with the Join node specfying lir as the join type. I would have to explicitly sort all of the input nodes, but after that the results of the Join node should stay sorted so sorts at each of the intermediate steps would no longer be necessary.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sassy1

Thank you for the information. It's nice to know I was on the right track....

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Pavan

Hi Can someone tell me what should be the inputExpr that we can give for Duplicate Detection node..I have quite a few duplicates and want to remove them, Can I just give one column name or do I need to mention all the data columns in this expr?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

You only need to specify the column names that make the records duplicate.
However.. the node you are asking about only DETECTS duplicates - it does not remove them.

You may instead want to use the SORT node with the UNIQUE option.