Forum Discussion
Remove duplicates without changing values in calculated column
I have a bit of a tricky situation and I am not able to figure out how to solve it. So I have a huge data set (million rows) and many duplicate IDs. The duplicate IDs are within the table because it shows results over different years. Without the duplicates I should have around 100 000 rows. However, I've added some calculated columns to the table that sums values of an ID over all the years combined.
Is there a way to remove the duplicate IDs without changing the result of the calculated columns?
I managed to solve this by uploading again the same data set under a different name. I've removed the duplicates in the second data set and used FIRSTNONBLANK function to add the calculated Tot column of the first data set to the second data set. A bit of an unconventional approach I would say, but it does the trick!
This idea was inspired by az38. Thank you!
7 Replies
I don't get your answer. Perhaps my problem was not very clear. Allow me to elaborate.
Consider the following example with just one ID:
where Tot is calculated as follows: Tot = calculate(sum(val), allexcept(ID)).
I want to remove the duplicate IDs and just keep one ID without the Tot column to change. Now, if I remove the duplicate ID rows with edit query, I get that Tot=10, but I want the final result to look like this:
