Apex Code Development You need to sign in to do that
Don't have an account?
Question about SAQL Wave Queries
I am struggling to figure out how to achieve a particular query using Wave and SAQL. I know how I would do it in Apex/SOQL but can't quite figure out the correct steps/syntax to get it to work in SAQL. Any help would be greatly appreciated. Here is a description of what I need to do. I need to find all the records of a custom object Revenue Schedule that have a Schedule Date in Last Quarter or This Quarter. The Revenue Schedule object has a lookup to an Opportunity and through that, you can get to the Account. Then I need to put these schedules into buckets based on Account association.
For each Account that has Schedules in LAST and THIS Quarter, add its Revenue to Revenue Bucket #1 and increase count of Active Accounts by 1 (unless this is the 2nd Schedule for this Account in which case only add Revenue, do not update count)
For each Account that has a Schedule in LAST Quarter but not THIS Quarter, add its Revenue to Revenue Bucket #2 and increase count of Inactive Accounts by 1 (unless this is the 2nd Schedule for this Account in which case only add Revenue, do not update count)
For each Account that has a Schedule in LAST Quarter and THIS Quarter, add its Revenue to Revenue Bucket #3 and increase count of Existing Accounts to 1 (unless this is the 2nd Schedule for this Account in which case only add Revenue, do not update count) .
So I have tried to run multiple queries but still cant wrap my head around the conditional logic for only updating the Count if it is the first Account of that type. I have tried using cogroup and group by AccountId but that gives me a single row in the Result Set for each Account. I can group them by Quarter which gets me 1 row for each Quarter but how to compare the values in those two result sets?
Is it possible to do a nested foreach?
For each Account that has Schedules in LAST and THIS Quarter, add its Revenue to Revenue Bucket #1 and increase count of Active Accounts by 1 (unless this is the 2nd Schedule for this Account in which case only add Revenue, do not update count)
For each Account that has a Schedule in LAST Quarter but not THIS Quarter, add its Revenue to Revenue Bucket #2 and increase count of Inactive Accounts by 1 (unless this is the 2nd Schedule for this Account in which case only add Revenue, do not update count)
For each Account that has a Schedule in LAST Quarter and THIS Quarter, add its Revenue to Revenue Bucket #3 and increase count of Existing Accounts to 1 (unless this is the 2nd Schedule for this Account in which case only add Revenue, do not update count) .
So I have tried to run multiple queries but still cant wrap my head around the conditional logic for only updating the Count if it is the first Account of that type. I have tried using cogroup and group by AccountId but that gives me a single row in the Result Set for each Account. I can group them by Quarter which gets me 1 row for each Quarter but how to compare the values in those two result sets?
Is it possible to do a nested foreach?
Shmuel Kamenskybump
Deepak BusnagariThe best possible solution for this is creating the buckets at the dataset level. You can create a separate 3 columns by defining the required condition. Hope this is helpful.