Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andymanu
Contributor

Using Flags

Please find the below sample data set ai have created

   

Prod_IDProd_NameQuantityCountry
P100A10USA
P101B15NZ
P103D20AUS
P101B12FR
P105E15NZ
P104F8FR
P106G10AUS

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

2 Replies
mikaelsc
Contributor III

Re: Using Flags

that's because your unique key between the 2 tables is not country & quantity, but it should be product_id and country

andymanu
Contributor

Re: Using Flags

Thanks Mikael.

After adding the Prod_ID to the left join table, it worked.

Cheers.