1

I'm trying to create a table in Wave analtycs— 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...

3

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.

Einstein Analytics dataset

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

Saql inside Compute Expression step

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

Augment Step 1 of 2 Augment Step 2 of 2

Your JSON for the dataset should look something like this:

Json code 1 of 3 Json code 2 of 3 Json code 3 of 3

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

Dataset output

  • Thanks ...but how putting 1 in SAQL calculates count? – Ayub Nov 30 '18 at 4:22
  • The Count is calculated by the Augment step in the Dataflow. It is essentially doing a 1:Many join and summing each child record as it goes into the new field that is created 'CountOpps'. – Andy_EA Nov 30 '18 at 4:37
  • This works? I previous tried this but got it to work involving a flatten transformation. Will try again! – Scott Pelak Dec 3 '18 at 15:38
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.