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: 
A7R3
Contributor III
Contributor III

Using IF and Match as a filter to display certain row in a straight table

Hi,

I use straight table a lot as I use it to prepare standard report with certain filtering condition. It has been working well so far. One of the easy example is as follow

=If(Match(Status, 'Red', 'Yellow')>=1 AND SpecTime >= ProductReportTime, PartNumber)

This will help to display the part numbers where the status is Red or Yellow, after certain time. All the PartNumber with status = green will be hidden.

Now I would like to display PartNumber with the Status = green, if the same PartNumber also has other line items that is yellow or red. In another words, I would like to exclude the PartNumber that has Status = Green only or Status is not equal to Green.

Data:

PartNumber   Supplier            Status    

A1                        S1                        Red
A1                        S2                        Yellow
A2                        S3                       Green
A2                        S4                       Red
A2                        S5                       Yellow
A3                        S6                       Green

The output should be

PartNumber   Supplier            Status    

A2                        S3                       Green
A2                        S4                       Red
A2                        S5                       Yellow

The  row with A1 should not appear because it does not have any line with Status = Green.
The row with A3 should not  appear because it only has one line with Status = Green.

Appreciate any suggestion or comment that I can try.

Thanks in advance.

Regards, Ernest

Labels (3)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try:

=Aggr(if(Count(Distinct {<Status={"Green*"}>}Status) and Count(Distinct {<Status-={"Green*"}>} Status)>0,PartNumber), PartNumber)

View solution in original post

5 Replies
tresesco
MVP
MVP

Try this calculated dimension:

Aggr(if(Count(Distinct {<Status={Green}>}Status) and Count(Distinct Status)>1,PartNumber)PartNumber)

A7R3
Contributor III
Contributor III
Author

Hi Tresesco,

Thank you for the help. With some minor tweak, the QS accepted the statement.

Aggr(if(Count(Distinct {<Status={'Green'}>}Status) and Count(Distinct Status)>1,PartNumber)PartNumber)

I have an idea how this works, and it is a promising concept.

However, it did not work as intended, because I try to simplify the actual situation as the example.

My actual data need to include 'Green_1' and "Green_2'. So the acceptable condition are the following

* Green_1 and Yellow
* Green_1 and Red
* Green_1 and Yellow and Red
* Green_2 and Yellow
* Green_2 and Red
* Green_2 and Yellow and Red

The following are to be excluded:

* Green_1
* Green_2
* Green_1 and Green_2

I think the issue with Count(Distinct Status)>1. It will accept Green_1 and Green_2.

Is there any way to improve the aggr statement?

Thanks in advance.

Regards, Ernest

 

tresesco
MVP
MVP

Try:

=Aggr(if(Count(Distinct {<Status={"Green*"}>}Status) and Count(Distinct {<Status-={"Green*"}>} Status)>0,PartNumber), PartNumber)

A7R3
Contributor III
Contributor III
Author

Hi Tresesco,

This works very well. Thank you very much.

My next challenges is with the timestamp and I wish to filter further.

This is to keep (Because Red/Yellow are the newer data)

PN   Status        SpecTime

A1   Red              08/20/2020 
A1   Green_1     07/03/2020

This is to exclude (Becasue the Red/Yellow are the older data)

A2   Yellow         07/03/2020
A2   Green_2     08/20/2020

Where the ProductReportTime = 08/20/2020 = The timestamp of the most recent data.

I tried with this, and it works. 

=Aggr(if(          Count(Distinct {<Status={"Green*"}>}Status)
                   and Count(Distinct {<Status-={"Green*"},  ReportTime={"$(=ProductReportTime)"}>}  Status)>0
                                         ,PartNumber), PartNumber)

However, the problem with this is that it only show the correct result when I choose 1 product.

What happen is that I load all the report to create a first table with all the p/n with the timestamp = ReportTime
I created another table from the first load to determine the timestamp of latest report for each product = ProductReportTime.

The table _1 will look like this

Products        PartNumber   Supplier    Status        ReportTime  

Product_1      A1                      S1                 Yellow        07/03/2020
Product  1      A1                      S2                  Yellow        07/03/2020
Product_1      A1                      S1                  Green          08/20/2020
Product  1      A1                      S2                   Yellow        08/20/2020

The table_2 will look like this

Products     ProductReportTime

Product_1  08/20/2020              <---- The latest report date for Product_1, because 08/20/2020 is newer 
Product_2  08/21/2020
Product_3  08/23/2020

The two tables are associated with "Products".

When I select any 1 product, the report is correct. When I select more than 1 product, the report becomes empty. I am guessing that is because each product has its own ProductReportTime, and some PartNumber are common to several Products. Is there anyway to fix this?

The above solution is already very helpful. If the statement can filter based on the timestamp further, it would be perfect.

 

Thank you in advance.

A7R3
Contributor III
Contributor III
Author

After thinking through it, I think the issue is with the fact that some parts are common to several products. So QS won't know which ProductReportTime to use for comparison, and will return nothing.

I tried to use Max(ProductReportTime) to get the most recent date. However, it will use the maximum value for all the reports for all parts, so the result will be wrong.

The correct logic would be to get the Max(ProductReportTime) for those products that have common parts, for each parts.

Something like Max (ProductReportTime for Product_1, ProductReportTime for Product_2, ProductReportTime for Product_3, ...)  for All Product that has A1. 

The challenge here is that each part may be common to 2 Products, or 20 Products. 

Alternatively, if there is a way to identify the correct ProductReportTime for each part during loading, it would be a good consideration as well. Is this going to be a simpler route?

 

Thanks in advance.