SOLVED
Regarding Joins and group by
Hi, I am attaching a use case with my query. I want to target only those customers who have orders amount of more than 5000 and want to put group by on customerId. I have made an alias (amount * order quantity) inside enrichment and put the condition of totalamount>5000 inside split activity I am unable to put group by on customerId can you please guide me for this. Immediate help will be highly appreciated.
1 Accepted Solution
Correct answer by
Hello @Mrityunjay ,
If you want to target if one order's total amount > 5000 then you can use like below.
1. Start your first query with the order table. First select all the order where ( amount * order quantity) >5000.
2. Then use change dimension activity to switch to recipient. It is important to select single records.
3. Your workflow will be look like below:
If you want to target, if total amount of all orders >5000 then you need to use group by.
For this one you can do like below:
1. In order query activity select all orders, do not add any condition.
2. Add additional data.
3. Disable adding primary key:
4. Add condition to select only records where total amount more than 5000.
5. After that you would get the customers who total amount is greater than 5000.
6. There is no targeting dimension in the output. If you want to add recipient dimension then use enrichment query:
it will look like below:
Thanks.
Parvesh.
Correct answer by
Hello @Mrityunjay ,
If you want to target if one order's total amount > 5000 then you can use like below.
1. Start your first query with the order table. First select all the order where ( amount * order quantity) >5000.
2. Then use change dimension activity to switch to recipient. It is important to select single records.
3. Your workflow will be look like below:
If you want to target, if total amount of all orders >5000 then you need to use group by.
For this one you can do like below:
1. In order query activity select all orders, do not add any condition.
2. Add additional data.
3. Disable adding primary key:
4. Add condition to select only records where total amount more than 5000.
5. After that you would get the customers who total amount is greater than 5000.
6. There is no targeting dimension in the output. If you want to add recipient dimension then use enrichment query:
it will look like below:
Thanks.
Parvesh.
Hi Parvesh,
Thank you so much for paving the path,
Sorry for the misunderstanding actually now I don't have to calculate the total amount(amount*quantity). The amount column has the total amount data. Now I have to sum the amount of all purchases by a particular customer and put group by on the customerId and if the customer purchase amount is greater than 5000 then he will receive a mail. Let me share with you the CSV file containing the data, It may clear the scenario.
So basically we have to sum the rows(TotalAmount) and group by CustomerId. Can you please help me with it? Please let me know in case any further clarification is needed.
Related Conversations
