Condition MMAX timestamp row

[desmond_lee]

Hi Guys

Is it possible to filter the SECOND max under the condition - result = FAIL ? In normal max this table expression will be below what about I want to filter row 3, (10/3/23, 4:26 PM   115G0-010310-01   FAIL) 

AND([TIMESTAMP]=MAX(SELECT(Table[TIMESTAMP],
[_THISROW].[PART NUMBER]=[PART NUMBER])),
[RESULT]="FAIL")
)

[Suvrutt_Gurjar · Accepted solution]

The requirement is complex because it deals with multiple parts and it will be a moving list because the user will keep adding new inspection records. So I believe one cannot avoid some complex multi row expressions. I believe it is a complex indexing requirement.

Another alternative is , please create a slice called say "Latest_Pass_Records" with an expression something like 

IN([Key column],

            SELECT(Inspection[Key column],

                                AND([SAP_NUMBER]=[_THISROW].[SAP_NUMBER]                                                                                              [INSPECTION_STATUS]="PASS",

                                         [INSPECT TIMESTAMP]> MAX(SELECT(Inspection[INSPACT TIMESTAMP],               

                                                                                                 AND([SAP_NUMBER]=

                                                                                                           [_THISROW].[SAP_NUMBER],
                                                                                                           [INSPECTION_STATUS]="FAIL"

                                                                                                           )

                                                                                                    )

                                                                                           )

                                                 )
                                  )

               )

Then the count of latest "PASS" record till the first FAIL can be given by the following VC expression

COUNT(SELECT(Latest_Pass_records[Key column], [SAP_NUMBER]=[_THISROW].[SAP_NUMBER]))

The above approach will reduce burden on VC expression as it will compute based on only latest pass records. But still please be aware that the slice expression is an inefficient one with a SELECT() in SELECT() and any SELECT() in a VC is sync time expensive.

So you could evaluate computing these expressions only on demand by say an action or bot so that you eliminate the recursive calculation through VC during each sync.

[Suvrutt_Gurjar · Accepted solution]

---

@desmond_lee wrote:

Are count the latest PASS until the FIRST FAIL

---

Yes. Please see carefully that you need to retain the slice as recommended earlier.

Also as mentioned, you may need to do some experimentation at your end. It will be difficult to propose an exact configuration in the post because your other app configuration you know the best.

[Suvrutt_Gurjar]

Please try below

Create a slice called say "Failed_Results" on the table with an expression something like [RESULT]="FAIL"

The slice will ensure that the SELECT() expression suggested below runs on much fewer records.

Then your expression for the Second highest time stamp can be 

[TIMESTAMP] =INDEX(SORT( SELECT(Failed_Results[TIMESTAMP],
[_THISROW].[PART NUMBER]=[PART NUMBER]), TRUE) ,2)

Please note that in general such SELECT() expressions can consume large sync time. If you could mention where you are using the expression, one could possibly try to reduce the SELECT() expression to run still on fewer rows.

[desmond_lee]

wow very impressive. I will try later after my meeting. Thnk you very much

[desmond_lee]

Hi Again

Yes I am able to ahieved. I use a slice to filter it and I have only 1 row that htat 3 times reject. Show below row # 433. But this still far from what I want to achieve. 

How do I filter off max timestamp for PART NUMBER that never fail below in the above table?

[Suvrutt_Gurjar]

It is not clear what screenshot you have shared- is it the screenshot of the slice or the expression?  Also it seems to be returning the row with the timestamp of 4:26 PM that you shared in the earliest post. Could you properly elaborate? 

[desmond_lee]

That is the screenshot of the slice. That is the correct row been filter.

Second max value below condtion fail is correctly filtered as per your expression

[Suvrutt_Gurjar]

Then could you update what further you are looking for?

Has your requirement been addressed?

[desmond_lee]

Hi Surrut

There is something I am still thinking and don't how to do. If you can help me that will be a BIGGGG thank to you because it save so much of the inspection time.

Atually what I want is to count the number of inspection by counting the part number (count repeating col B part number) before the last fail inspection. If we count by criteria of col C fail there following condition need count. (1) Col B part number CONSECUTIVELY PASS mean never fail in any inspection. Example Row 5. & 22 10/3/23, 4:32 PM 115G0-002029-01. The inspection is happening in Row 5 & Row 22 both is PASS  consecutively i.e. NEVER FAILED in between then its a direct count is 2 (2) Count from the lastest inspection until the last fail inspection row. Example Row 2,3,4 & 20. 10/3/23, 4:23 PM 115G0-010310-01. Notice there are 4 inspections & in Row 3 is FAIL. The we count the number of inspection from Row 4 until the latest Row 20. The answer here is 2.

So question here is how do I create one single warp up expression that give the answer base on 2 conditons as mention in above?

[Suvrutt_Gurjar]

Thank you but I think there is some discrepancy in your description?

---

@desmond_lee wrote:

Example Row 2,3,4 & 20. 10/3/23, 4:23 PM 115G0-010310-01. Notice there are 4 inspections & in Row 3 is FAIL. The we count the number of inspection from Row 4 until the latest Row 20. The answer here is 2.

---

Not sure why count should be 2 here. This is so because row 3 is FAIL and row 20 is FAIL. So only Row 4 is PASS in between.  So count should be 1? Please elaborate with more examples. 

Is it correct understanding that you wish to calculate PASS instances of a part between two FAIL instances?

[desmond_lee]

Thousand appologies it's my mistake. Row 20 is PASS value not a FAIL value. The number of inspection is count 2. Why the count is not 1  ! Because the criteria is count from the Max Row timestamp that is Row 20 the to last failed value that is Row 3. So that means count backwards between Row 20, Row 4 for a PASS is 2.

Is it correct understanding that you wish to calculate PASS instances of a part between two FAIL instances?  Ans : Yes you are right

[Suvrutt_Gurjar]

Thank you but what count you would expect if the row 20 was FAIL result.  In this case MAXROW is PASS but what result you would expect if it were FAIL

While writing expression I believe you will need to consider all such options.

[desmond_lee]

Thank you but what count you would expect if the row 20 was FAIL result. In this case MAXROW is PASS but what result you would expect if it were FAIL Ans: Beccause the criteria count form the Max row timestamp when Row 20 is FAIL there is nothing to count under the PASS critieria the answer is 0.

[Suvrutt_Gurjar]

Please try below. Please note the following,

Please test well for all test cases. Also please note that such indexing expressions will need to run on the large number of rows and will involve some multirow expressions. As such, if you are using it in VC , please add additional logic to calculate the VC only when requested by user and not to have to calculate the VC during each sync.

In general what you are looking for can be found by the following expression.

COUNT(SELECT(Table Name[Result], [PART NUMBER]=[_THISROW].[PART NUMBER]))-
COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & SELECT(Table Name[Result], [PART NUMBER]=[_THISROW].[PART NUMBER]) & " , "),
(" , " & "FAIL" & " , ")
)
),
" , "
)
)

The expression makes use of INDEXOF() expression suggested in the nice tip below by @Steve 

https://www.googlecloudcommunity.com/gc/Tips-Tricks/INDEXOF/m-p/427993

[desmond_lee]

Hi Suvrutt

I created a Virtual Column and try out the expression. It does not seems to work. See below.

Actual Raw Data (Column A to Column H

I manually put this to a summary table to compare the result

Virtual column

[Suvrutt_Gurjar]

---

@Suvrutt_Gurjar wrote:

In general what you are looking for can be found by the following expression.

---

As I mentioned , it was a general suggestion of using indexing to find the exact number of the first fail.

For specifics, you will need to modify the approach. You may want to try the following.

Please create a VC called say [Reverse_Order] with an expression something like

ORDERBY(Inspection[INSPECT ID], [INSPECT TIMESTAMP], TRUE)

This column's settings should be - list type,  element type as reference and Reference table name as "Inspection". The expression assumes that [INSPECT ID] is the key column of the  "Inspection" Table. or please replace with the key column of the table.

Then create a VC called say [FIRST_FAIL] of number type with an expression something like 

COUNT(SPLIT(LEFT(SELECT([Reverse_Order][INSPECTION STATUS], [SAP NUMBER]=[_thisrow].[SAP NUMBER]), ,FIND("FAIL",SELECT([Reverse_Order][INSPECTION STATUS], [SAP NUMBER]=[_THISROW].[SAP NUMBER]))-3)," , "))

This [FIRST_FAIL] column should capture the first instance of FAIL for a particular [SAP NUMBER]

As usual, please be aware that such multi row expressions in a VC will impact the app sync time. Please try to minimize the calculation load on SELECT() . for that you could create a slice on the "Inspection Table' to say filter records of only last one month. You could then run the suggested expressions on that slice instead of the entire table.

[desmond_lee]

There are over 7000 row of data and the sync is way too long. I am thinking of another way. ORDERBY(Inspection[INSPECT ID], [INSPECT TIMESTAMP], TRUE) is not suitable for me process

[Suvrutt_Gurjar]

Okay, thank you. I had anyway mentioned the possible sync time issue with such multirow expressions. What other way you are thinking of? please do update us.

[desmond_lee]

Thank very much.

We make SAP number 122G0-003102 as example 

There is a method and I don't workable or not. If we look at the latest FAIL at ROW 11. I am able to filter off successfully using this expresion AND([INSPECT TIMESTAMP]=MAX(SELECT(SAP Fail[INSPECT TIMESTAMP],
[_THISROW].[SAP NUMBER]=[SAP NUMBER])). Row ll date is 8/1/23, 8:14 AM. Are we able to make a slice that pull out all SAP # after this date 8/1/23, 8:14 AM? And after if this is able we just count that number of pass ME LOT NUMBER. The key is ME LOT NUMBER. Key word is count after the latest SAP #  fail timestamp will work

[Suvrutt_Gurjar · Accepted solution]

The requirement is complex because it deals with multiple parts and it will be a moving list because the user will keep adding new inspection records. So I believe one cannot avoid some complex multi row expressions. I believe it is a complex indexing requirement.

Another alternative is , please create a slice called say "Latest_Pass_Records" with an expression something like 

IN([Key column],

            SELECT(Inspection[Key column],

                                AND([SAP_NUMBER]=[_THISROW].[SAP_NUMBER]                                                                                              [INSPECTION_STATUS]="PASS",

                                         [INSPECT TIMESTAMP]> MAX(SELECT(Inspection[INSPACT TIMESTAMP],               

                                                                                                 AND([SAP_NUMBER]=

                                                                                                           [_THISROW].[SAP_NUMBER],
                                                                                                           [INSPECTION_STATUS]="FAIL"

                                                                                                           )

                                                                                                    )

                                                                                           )

                                                 )
                                  )

               )

Then the count of latest "PASS" record till the first FAIL can be given by the following VC expression

COUNT(SELECT(Latest_Pass_records[Key column], [SAP_NUMBER]=[_THISROW].[SAP_NUMBER]))

The above approach will reduce burden on VC expression as it will compute based on only latest pass records. But still please be aware that the slice expression is an inefficient one with a SELECT() in SELECT() and any SELECT() in a VC is sync time expensive.

So you could evaluate computing these expressions only on demand by say an action or bot so that you eliminate the recursive calculation through VC during each sync.

[desmond_lee]

Thank you very much. Wow it works perfect and I compare manually filtering result side by side it 100% correct. Just one thing is that sometimes the inspection form loading 2 to 4 seconds. There are 8,215 rows now

[Suvrutt_Gurjar]

---

@desmond_lee wrote:

Just one thing is that sometimes the inspection form loading 2 to 4 seconds.

---

Yes, such an expression will take a long time on a large table. Please do evaluate moving it to BOT or action so that you calculate it only on demand.

Please test well.

[desmond_lee]

I don't have any idea of how to do that. Maybe convert the calculation VC latest record in a real column and create the largest value in a slice to reduce the number of unwanted rows

[Suvrutt_Gurjar]

Yes, you could explore below.

Please add a real column called say [First_Fail_Count] of number type.

Create an action of type Data: Set values of some columns in this row" that sets the  [First_Fail_Count]  column with an expression 

COUNT(SELECT(Latest_Pass_records[ID], [SAP_NUMBER]=[_THISROW].[SAP_NUMBER]))

"Only if this condition is true" setting of this action can be something like 

[Key column]=MAXROW("Inspection", "INSPECT TIMESTAMP", [SAP_NUMBER]=[_THISROW].[SAP_NUMBER])

You could have the action as an inline action in a summary view.

This is just a rough idea and at basic level. Actual implementation will depend upon your app configuration, user behavior and other factors. It will be definitely involve more complexity in actual implementation.

[desmond_lee]

Sound very convincing. I will try this later. Thank you very much

[desmond_lee]

Kinda confuse here. Are count the latest PASS until the FIRST FAIL row or we are filter the first fail ROW?

[Suvrutt_Gurjar · Accepted solution]

---

@desmond_lee wrote:

Are count the latest PASS until the FIRST FAIL

---

Yes. Please see carefully that you need to retain the slice as recommended earlier.

Also as mentioned, you may need to do some experimentation at your end. It will be difficult to propose an exact configuration in the post because your other app configuration you know the best.

[desmond_lee]

Hi Suvrutt

I may not be continue this part of the process because its not suitable for my process. When an inspector fill up an inspection form as soon as he key in the SAP NUMBER he need to see the LAST PASS COUNT in live. So if we use automation as set value you cannot use formula to retrieve the last pass count. ACTION cell cannot contect expression. I dont think we have much option here. Thank you

And I manage to create that but I will delete it off. See below column STS (6 CONSECUTIVE LOTS PASS)_V1 is automation

[Suvrutt_Gurjar · Accepted solution]

---

@desmond_lee wrote:

an inspection form as soon as he key in the SAP NUMBER he need to see the LAST PASS COUNT in live.

---

Well in that case I will try the following approach.

In the [First_Fail] column , I will try an initial value expression something like 

COUNT(SELECT(Latest_Pass_records[Key column], [SAP_NUMBER]=[_THISROW].[SAP_NUMBER]))

If [First_Fail] is a real column I will save it as a form save event action when the said form is saved.

I will set the [First_Fail] column to 

COUNT(SELECT(Latest_Pass_records[Key column], [SAP_NUMBER]=[_THISROW].[SAP_NUMBER]))

in the action expression.

[desmond_lee]

Wow I need to digest this. THANK YOU

[desmond_lee]

"initial value...." You are referring this location ?

[Suvrutt_Gurjar]

Yes.  The "initial value" of column where you are storing the first fail counts. 

Please test well for your requirement as I may not not know certain other configurations / requirements in your app.

[desmond_lee]

"The "initial value" of column where you are storing the first fail counts. ......." In this case the first fail count are you referring the first count in on the current form that I submit is store here?

[Suvrutt_Gurjar]

The column where you are storing the counts "6 , 5 or 10"  of PASS counts till last failure of a [SAP_NUMBER]

[desmond_lee]

Below is the ACTION and the expression IF(AND(
COUNT(SELECT(Latest_Pass_Records[MES LOT NUMBER], [SAP NUMBER]=[_THISROW].[SAP NUMBER]))
=0,[INSPECTION STATUS]="PASS"),1,
COUNT(SELECT(Latest_Pass_Records[MES LOT NUMBER], [SAP NUMBER]=[_THISROW].[SAP NUMBER]))
)

Athe INITIAL VALUE and the expression IF(AND(
COUNT(SELECT(Latest_Pass_Records[MES LOT NUMBER], [SAP NUMBER]=[_THISROW].[SAP NUMBER]))
=0,[INSPECTION STATUS]="PASS"),1,
COUNT(SELECT(Latest_Pass_Records[MES LOT NUMBER], [SAP NUMBER]=[_THISROW].[SAP NUMBER]))
)

The result as below. STS (6 CONSECUTIVE LOTS PASS) is the VC for verification and STS (6 CONSECUTIVE LOTS PASS)_V1 is the automated bots. See below all working OK. Side by side comparisan between VC column and Automate Bots

[Suvrutt_Gurjar]

Not sure what you mean. Is it working as required now?

Not sure why you needed to do those modifications to the expression. But as long as suggested approach is working ("Initial Value" and action) in your testing, I believe you are good to go.

[desmond_lee]

Its all working. Thank you

[Suvrutt_Gurjar]

Great. But please test for all possible conditions, such as opening an existing record, adding a new record and so on. Intial value works only with new record. So you may need tweaks such as using " reset on edit " for existing record and so on. It is simply not possible to know all conditions in discussion here. Such complex functionalities will need thorough testing. 

[desmond_lee]

Suvrutt

Thank very much. You are awesome. I have another question regarding chart posted separately