Duplicate Sales

[its_rajvir]

Dear Experts,

I have data like this :

Customer ID	Transaction	Type
11	Sale	CS
11	Sale	PD
11	Entry	UI
11	Exit	UO
12	Sale	CS
12	Sale	PD
12	Entry	UI
12	Exit	UO
12	Entry	UI
12	Exit	UO
12	Sale	CS
13	Sale	CS
13	Sale	PD
13	Entry	UI
13	Exit	UO
13	Sale	CS
14	Sale	CS
14	Sale	PD
14	Entry	UI
14	Exit	UO

From this data i want to extract the customers IDs having Transaction as Sale and Type and CS more than one time.

The output should be customer ID 12 And 13

Can anyone please help 

[Mark_Little · Accepted solution]

Hi,

In script i would flag them with a resident load.

LOAD

[Customer ID],

IF(COUNT(Type) > 1, 1) AS Flag

Resident Table

Where Transaction = ' Sales' and Type = 'CS'

You can use this flag in the front end set analysis to pick out the customers you are interested in.

Group by [Customer ID];

[its_rajvir]

Thankyou @Mark_Little . I got the correct output 😍

[its_rajvir · Accepted solution]

we can also do like this :

[Table1]:
LOAD
"Customer ID",
Transaction,
"Type"
FROM [lib://desktop/Duplicate Sales.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Table2]:
Load
*,
Count("Type") as CS_Count
Resident [Table1]
where "Transaction" = 'Sale' and "Type" = 'CS'
Group By "Customer ID",Transaction,"Type";

[Final_Table]:
NoConcatenate
Load distinct *
Resident [Table2]
where CS_Count > 1;
Drop Table [Table1];
Drop Table [Table2];