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”)
)
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.
wow very impressive. I will try later after my meeting. Thnk you very much
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?
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?
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
Then could you update what further you are looking for?
Has your requirement been addressed?
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?
Thank you but I think there is some discrepancy in your description?
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?
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
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.
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.
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
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
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.
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
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.
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
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.
