Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone! I need help figuring out a simple problem (I think): I have a table where each row shows the quote document created by an agent for a customer and its timestamp. Of course, every quote, agent, and customer document has a unique ID.
When the same agent creates multiple quote documents on the same customer in the same month, those quote documents must be rejected, otherwise they must be taken into account.
So, how can I create a flag that tells me if there is more than one estimate document created by the same agent for the same customer in the same month?
Thank you in advance
Like this ?
test:
load * Inline
[
agent,customer,month,region
abc,aditya,jan,AUS
abc,aditya,jan,USA
xyz,soham,feb,JPN
xyz,aditya,feb,IND
lmn,rohit,mar,BRZ
];
left join (test)
test_2:
load
agent,
customer,
month,
count(1) as distinct_count
resident test group by agent, customer, month;
drop table test;
Regards,
Aditya
Hi all,
thanks a lot but these solutions don't work ( I mean I didn't able to do).
I try to explain better:
i need to count if, in the same month, the same agent on the same customer has done two quote documents. If yes, i need to rejected.
Example:
Quote Document | Agent | ID Costumer | Timestamp |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
In this case, the quote document 85876 (in red) must be rejected because i had yet a quote document on the same customer done by the same agent. I need something that counts the max record on the same customer on the same agent.
Thank you again in advance
Hi,
thanks a lot but these solutions don't work ( I mean I didn't able to do).
I try to explain better:
i need to count if, in the same month, the same agent on the same customer has done two quote documents. If yes, i need to rejected.
Example:
Quote Document | Agent | ID Costumer | Timestamp |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85876 | F269 | 1523072 | 04/01/2023 10:47:15 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
85877 | F269 | 1523072 | 04/01/2023 10:47:45 |
In this case, the quote document 85876 (in red) must be rejected because i had yet a quote document on the same customer done by the same agent. I need something that counts the max record on the same customer on the same agent.
Thank you again in advance