Creating a search index to get all the products assigned to a particular supplier

[Manasareddy09]

Hello Everyone,

Greetings of the day 🙂

I am trying to Update a Supplier Status  for which i have a condition. 

I have attached Sample Data, In which i have 3 tables.

1.Supplier_View : With all the information of a supplier

2.Product_View: Which has all the information of ALL products &

3.PPQ_Product_View: Which has information of ALL products which have PPQ Status.

The "IFA" is the field which can be used to connect both the tables. Every supplier has a unique IFA and in the Product Table, Products coming from the same supplier will have Same IFA As it is unique to the supplier. 

I want to get the STATUS OF A SUPPLIER BY THE FOLLOWING CONDITION:

if MSA_STATUS="OK" and if the Supplier has a product and ATLEAST one of the Products Assigned to that Supplier has PPQ status="OK" then it should show "OK", Else it should show "NOK".

I have used Count function to know if the supplier has a product or No by grouping it with IFA. The only issue i have is that i Dont know how to ask Qlik to check PPQ Status of all the products assigned to  Particular supplier , and show OK if atleast one of them are OK. otherwise i want to see "NOK" for a Supplier.

I have been stuck with this problem this whole week. I cant find out a solution for it. 

Please write if you dont understand something. 

Thank you for your valuable time in advance.

#ifelse #joins 

[edwin · Accepted solution]

from the data it appears IFA is also a supplier identifier and your PPQ table differentiates the same product from different suppliers so your key should be combination of PRODUCT and IFA.

SUpplier:
LOAD IFA, 
     Supplier_Name, 
     Supplier_ID, 
     MSA_Status
FROM
[...\Sample Data.xlsx]
(ooxml, embedded labels, table is Suuplier_View_Table);

NoConcatenate
Products:
LOAD IFA, 
     //Supplier_Name, 
     Product_Name,
     IFA & '|' & Product_Name as ProdKey
FROM
[...\Sample Data.xlsx]
(ooxml, embedded labels, table is Product_View_Table);

PPQ:
LOAD 
//IFA, 
    // Product_Name, 
    IFA & '|' & Product_Name as ProdKey,
     PPQ_STATUS, 
     OVERALL_PPQ
FROM
[...\Sample Data.xlsx]
(ooxml, embedded labels, table is PPQ_Product_View);

and in your chart you can use supplier as the dimension and 

=if(count(distinct {<MSA_Status={'OK'}, PPQ_STATUS={'OK'}>}Supplier_Name)>sum({1}0),'OK','NOK')

[Manasareddy09]

Hello Edwin,

Thank you fir taking time. The solution works fine but when i add this to the chart it does not allow me to select a particular supplier . For example if i am trying to select one supplier from the list of supplier and apply the filter in usual scenario we only see that supplier's data. but in this it still shows me all the Supplier's by keep the selected ones in the top. 

And one more issue is that I want to get the percentage of supplier's which are "OK", But as i am not sure if i can get the Percentage of them in an extra column. That is why i wanted to create a column instead of a set formula. 

Thank you,

Bandari

[edwin]

to be able to show those that do not satisfy the criteria, the expression uses sum({1}0} which means ignore all selections.  this addresses the first defined requirements and not ones that arent defined

i think what is needed is an idea what the requirements are.  if we tweak this according to the new requirement without understanding what other requirements you have in mind, then we will just be going back and forth