Please find the below sample data set ai have created
| Prod_ID | Prod_Name | Quantity | Country |
| P100 | A | 10 | USA |
| P101 | B | 15 | NZ |
| P103 | D | 20 | AUS |
| P101 | B | 12 | FR |
| P105 | E | 15 | NZ |
| P104 | F | 8 | FR |
| P106 | G | 10 | AUS |
I want to calculate the sum of quantity where the Country is NZ and the product quantity is greater than 10.
I used the below specified script to accomplish the said task.
Products:
LOAD
Prod_ID,
Prod_Name,
Quantity,
Country
FROM [lib://AttachedFiles/Products.xlsx]
(ooxml, embedded labels, table is Products);
Left Join (Products)
LOAD Country,
Quantity,
'X' as FLAG
Resident Products
Where Country = 'NZ' and Quantity>10;
The FLAGS are working fine.
However, when I view the results from the Data Model Viewer, it shows a total of 9 records (rows) instead of 7 where the records for NZ with duplicate rows. But when replace the value of NZ to FR, it shows only 7 total rows (which is correct).
Please help me in below questions;
1. The reason for NZ record getting duplicated where FR is not? how to eliminate the issue?
2. I want to calculate the sum of quantity for NZ where the quantity is greater than 10. Please guide me how to use (may be KPI under the Charts) created flags to approach the answer?
Thank you in advance.
Regards,
Manu
