Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
_nappi
Contributor II
Contributor II

How to see if a record appears two times

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

Labels (3)
4 Replies
Aditya_Chitale
Specialist
Specialist

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

ASEDavidSu
Contributor III
Contributor III

ASEDavidSu_1-1669183335467.png

 

ASEDavidSu_0-1669183301308.png

 

_nappi
Contributor II
Contributor II
Author

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

_nappi
Contributor II
Contributor II
Author

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