How to get aggregated data without losing rows

user094816 · February 2021

I am looking to get count for each row without loosing any row.

It can be achieved easily in excel using count if in excel but here how I can implement in ETL or Beast mode?

Please see the sample input and output I am looking for in below image

jaeW_at_Onyx · February 2021

@user094816 , no if you create a beast mode on an aggregate, no you should not try to build a calculated column off of it. Sometimes it works, usually it gets weird.

you can create this in ETL using a GROUP BY with a COUNT, and then JOIN the GROUP BY tile back to the ungrouped data.

from there you can easily build a CASE statement using a Formula tile in Magic 2.0

If you don't have Magic 2.0, you can still use the GROUP BY appropach and then either implement the CASE statement using a blend of FILTER and APPEND tiles, or in a beast mode.

MarkSnodgrass · February 2021

@user094816 you can do this very easily in the ETL. See example in the image below.

Start with your dataset and then add a Group by tile and do a count of your column you want counted.

Add a join tile and connect it to your original dataset and your group by tile. Join on the appropriate key.

This will keep all your rows and show the total from the group by tile as well.

GrantSmith · February 2021

Hi @user094816

You can utilize a window function in a beast mode - you need to have this feature enabled in your instance. Talk with your CSM to enable window functions.

SUM(SUM(1)) OVER(PARTITION BY `Col1`)

This would get you 5 for every ABC123 record. In your example data you have ABC123 listed 4 times and then 1 time but the number of repeated is 4 for all 5 entries. Are you expecting it to be 4 or should it actually be 5?

user094816 · February 2021

Hi @GrantSmith , that was typo, I want it to be 5 only. This function works great in Beast mode. (can we create this in ETL?)

I have got another problem. Now I am not able to refer the output of this function to create custom Column in beast mode.

plz see the latest screenshot.

In Excel I have formula like this to achieve last column:

=IF(AND('Count no of Repeated'>1,'Channel'<>"Test Channel"),"Flag1","")

if above measure you mentioned can be created in ETL then I guess I can reference that column but right now it is beast mode output and I am not able to refer this one to another function.

jaeW_at_Onyx · February 2021

@user094816 , no if you create a beast mode on an aggregate, no you should not try to build a calculated column off of it. Sometimes it works, usually it gets weird.

you can create this in ETL using a GROUP BY with a COUNT, and then JOIN the GROUP BY tile back to the ungrouped data.

from there you can easily build a CASE statement using a Formula tile in Magic 2.0

If you don't have Magic 2.0, you can still use the GROUP BY appropach and then either implement the CASE statement using a blend of FILTER and APPEND tiles, or in a beast mode.

MarkSnodgrass · February 2021

@user094816 you can do this very easily in the ETL. See example in the image below.

Start with your dataset and then add a Group by tile and do a count of your column you want counted.

Add a join tile and connect it to your original dataset and your group by tile. Join on the appropriate key.

This will keep all your rows and show the total from the group by tile as well.

user094816 · February 2021

Thanks!! @MarkSnodgrass @jaeW_at_Onyx @GrantSmith . Now I am able to achieve what I wanted using ETL to generate the count and Beast mode to created calculated column based on output from ETL logic.

How to get aggregated data without losing rows

Best Answers

Answers

Categories