Regarding Joins and group by | Community
Skip to main content
M
Adobe Employee
MrityunjayAdobe Employee
Adobe Employee
June 9, 2022
Solved

Regarding Joins and group by

  • June 9, 2022
  • 1 reply
  • 1171 views

 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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Parvesh_Parmar

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.

1 reply

Parvesh_Parmar
Community Advisor
Parvesh_ParmarCommunity AdvisorAccepted solution
Community Advisor
June 10, 2022

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.

Parvesh Parmar – Adobe Community Advisor https://www.linkedin.com/in/parvesh-parmar/
    M
    Adobe Employee
    MrityunjayAdobe EmployeeAuthor
    Adobe Employee
    June 10, 2022

    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.

     

    Parvesh_Parmar
    Community Advisor
    Parvesh_ParmarCommunity Advisor
    Community Advisor
    June 10, 2022

    Hello @mrityunjay, 

     

    I have explained you two case in my last reply. 

    You can use the second solution to do the sum of all orders and group by customer Id .

    It start form the line "If you want to target, if total amount of all orders >5000 then you need to use group by. " from above reply. 

     

    There i mentioned "sum(@amount*@orderquantity)", if you have total amount then you can do the sum of totoalAmount like sum(@totalAmount)

     

    Thanks.

    Parvesh.

     

     

    Parvesh Parmar – Adobe Community Advisor https://www.linkedin.com/in/parvesh-parmar/
      Terms & ConditionsAccessibility statement

      Loading footer...