Calculating total against subtotal

user016969
user016969 Member
in Magic ETL

Hi,

I have a data set as follows:

IDNameCondition1Condition2Condition3Condition4
123ABC1101
245XYZ1011
789PQR0011

 

Which I need to represent as :

 Count%
Con1270
Con2134
Con3270
Con43100

 

I am using mysql and have converted the data as:

IDNameCondition1Condition2Condition3Condition4Condition
123ABC1000Con1
123ABC0100Con2
124ABC0001Con4
245XYZ1000Con1
245XYZ0010Con3
245XYZ0001Con4
789PQR0010Con3
789PQR0001Con4

 

But for the % calculation, I need the total number of records in beast mode. My beast mode:

Count(Condition)/ sum(count(distinct ID) over ( )

e.g. for Con1 % calculation should be 2/3 whereas when I use window function in beast mode it gives 2/8. Is there nay way to handle it in beast mode or mysql?

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user016969 

    You're close but instead of using a window across the entire dataset you're wanting it across each ID / Name.

     

    Just a minor change to your beast mode should get you what you want.

    Count(Condition)/ sum(count(distinct ID) over (PARTITION BY `ID`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user016969
    user016969 Member

    That is giving me 100% for all rows. I added a column just for the beast mode sum(count(distinct ID) over (partition by ID) and it gives same # as condition count.

     

     

  • GrantSmith
    GrantSmith Coach

    Instead of using COUNT try using SUM:

     

    SUM(Condition1)/ sum(count(distinct ID)) over (PARTITION BY `ID`)


SUM(Condition2)/ sum(count(distinct ID)) over (PARTITION BY `ID`)


SUM(Condition3)/ sum(count(distinct ID)) over (PARTITION BY `ID`)


SUM(Condition4)/ sum(count(distinct ID)) over (PARTITION BY `ID`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user016969
    user016969 Member

    That's giving 0%

  • user016969
    user016969 Member

    In the absence of other solution, through mysql I added a static column in table to store the total number of unique records and using that value in beast mode.

Categories