Cross tab data with duplicate records

[sgaditya]

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! 

 

 

[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.

M.

[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

[mceleavey · Accepted solution]

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.

M.

[sgaditya]

This actually worked!! 

Thank you so much for your solution. Really appreciate it 🙂 

[mceleavey]