1 Reply Latest reply: May 21, 2018 6:48 PM by Andy Manuja RSS

    Using Flags

    Andy Manuja

      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