topic Re: Using IF and Match as a filter to display certain row in a straight table in App Development

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

A7R3 — Sat, 16 Nov 2024 18:10:35 GMT

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

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

tresB — Fri, 28 Aug 2020 07:27:34 GMT

Try this calculated dimension:

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

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

A7R3 — Fri, 28 Aug 2020 08:17:03 GMT

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

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

tresB — Fri, 28 Aug 2020 09:56:15 GMT

Try:

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

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

A7R3 — Sat, 29 Aug 2020 00:00:20 GMT

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.

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

A7R3 — Wed, 02 Sep 2020 00:52:41 GMT

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.