Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ksharpes
Creator
Creator

Counting field values in Script

I have a number of "Products" sold by a number of "Customers" what i want to see is if a product is sold by more than one customer.

 

Data is as follows and I want to create the field "Multiple Sellers" via script

 

CustomerProductSalesMultiple Sellers
1Product A5Yes
1Product A5Yes
1Product B5Yes
1Product B5Yes
1Product C5Yes
1Product D5No
1Product D5No
2Product A5Yes
2Product B5Yes
2Product E5No
2Product E5No
3Product B5Yes
3Product C5Yes
4Product A5Yes
4Product A5Yes
4Product C5Yes
1 Solution

Accepted Solutions
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi @ksharpes 

This should work:

[Table]:
LOAD * INLINE [
Customer,Product,Sales,Multiple Sellers
1,Product A,5,Yes
1,Product A,5,Yes
1,Product B,5,Yes
1,Product B,5,Yes
1,Product C,5,Yes
1,Product D,5,No
1,Product D,5,No
2,Product A,5,Yes
2,Product B,5,Yes
2,Product E,5,No
2,Product E,5,No
3,Product B,5,Yes
3,Product C,5,Yes
4,Product A,5,Yes
4,Product A,5,Yes
4,Product C,5,Yes
];

LEFT JOIN

LOAD
IF(Count(DISTINCT Customer) > 1,'Yes','No') AS [Multiple Sellers],
Product
RESIDENT [Table]

GROUP BY Product;

Regards,

Mauritz

View solution in original post

2 Replies
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi @ksharpes 

This should work:

[Table]:
LOAD * INLINE [
Customer,Product,Sales,Multiple Sellers
1,Product A,5,Yes
1,Product A,5,Yes
1,Product B,5,Yes
1,Product B,5,Yes
1,Product C,5,Yes
1,Product D,5,No
1,Product D,5,No
2,Product A,5,Yes
2,Product B,5,Yes
2,Product E,5,No
2,Product E,5,No
3,Product B,5,Yes
3,Product C,5,Yes
4,Product A,5,Yes
4,Product A,5,Yes
4,Product C,5,Yes
];

LEFT JOIN

LOAD
IF(Count(DISTINCT Customer) > 1,'Yes','No') AS [Multiple Sellers],
Product
RESIDENT [Table]

GROUP BY Product;

Regards,

Mauritz

ksharpes
Creator
Creator
Author

Almost had it ><.

Thank you