How to add count of child records at parent row level in a table of Wave?

Question

I'm trying to create a table in Wave analytics— each row of table would show account record. Additionally, I want to show some extra columns for each record to keep child records count ..like count of Contact, count of Cases or count of Opportunity of that Account.

I'm not able to figure out where should I add child dataset; can I do that data flow level or directly I can do it in dashboard's SAQL. Or any other best workaround...

Answer 1

To count associated records in the Dataflow - simply add a Compute Expression node after the Object you wish to count. In this example we count the number of Opportunities for each Account.

In the Compute expression, add a Field called 'CountOpps' or similar. In the SAQL area for this field just type: 1

Remember to change the Precision to 10 and Scale to 2.

Next you want to Augment the Account to our 'CountOpps' expression. Make sure you keep Account as the Left Source and 'CountOpps' as the Right Source. Your join key should be Opportunity(AccountId) and Account(Id). Make sure you change the Operation to 'Look up multiple values'.

Your JSON for the dataset should look something like this:

The end result in Einstein Analytics should look something like this:

Answer 2

You can get the count using a couple of computeRelative and :

Add Count via computeRelative

Group by the Parent ID and order by Record ID (order doesn't matter).

Order

• Computed Field Name: Count
• Label: Count
• Expression Type: SAQL

• SAQL Expression:

  case
      when previous('Count') > 0 then previous('Count') + 1
      else 1
  end
  

Do computeRelative again to detect record with most Count

Group by the Parent ID and but order bu Count descending (very important). Create a field to detect the record with the highest count in grouping: IsLastCount

IsLastCount

• Computed Field Name: IsLastCount
• Label: Is LastCount
• Expression Type: SAQL

• SAQL Expression:

  case first('Count')
      when current('Count') then "true"
      else "false"
  end
  

Filter where IsLastCount is true

Filter: IsLastCount:EQ:true

Now you have one record per Parent ID and Count contains exactly what you need

Augment Child to Parent

Here's the augment transformation documentation