Cross tab data with duplicate records

Hi,

I am calculating a column using formula, but when i cross tab the table using the same column as group by field, I am getting duplicate where record count is doubled( can refer to the record id filed)

Any idea how to solve this as i want calculated new value to come with the actual records without duplicate?

Attaching screenshots of both with configuration of crosstab for reference

Thanks!

Workflow

Developer

Transformation

Accepted answers

mceleavey

Hi @sgaditya ,

In this case you will need to take a summary tool separate from the main stream before the crosstab tool and group by the key field, and using sum of the Quantity field. Then join this back to the main stream on the key field and replace the quantity field with the new summed quantity. You should now be able to group by the quantity field as your field will be summed and you should only have one value for each key.

All comments

mceleavey

Hi @sgaditya ,

Yes, this is correct. You are getting "duplicates" because you have different quantities and you're using that in the group by selection. This means you will have one row for each variation of the combination of those fields. If you un-check quantity, in this case you will get a single line if you use SUM as the aggregate function.

sgaditya

When i uncheck the quantity field, Ill lose the column from the table as it will be dropped.

Not sure how to use SUM function then. However, the record count is proper now but without the quantity field

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703