Filter table by Distinct ID and latest timestamp

[ericonline]

Hello,

Hoping someone can help me wrap my head around this. I need to filter the below table by Distinct equipmentID and latest timestamp.

The records highlighted below should be the results.

Sample data:

ID	equipmentID	timestamp	xvalue	yvalue
1	123	2021-03-03T060000Z	Test	Test2
2	123	2021-03-03T050000Z	Test0	Test4
3	345	2021-03-03T080000Z	TestTest	TEst
4	345	2021-03-03T070000Z	Yes	Yep
5	456	2021-03-03T080000Z	Sure	Uhuh

Something like:

ClearCollect(colLatestRecords,
    Filter(
        Distinct(TABLE_NAME, equipmentID),
        Max(timestamp)
    )
);

 But this is not correct. 

Any ideas?

Thank yoU!

[Drrickryp]

@ericonline 

Use AddColumns() for a formula that concatenate the equipment ID and the time stamp and use distinct on the new column.

[ericonline]

@Drrickryp , thanks for the reply. Where does the Distinct() come in? 

ClearCollect(colLatestRecords,
    Filter(
        AddColumns(TABLE_NAME,
            "uniqueLastRecord",
            Concatenate(ThisRecord.equipmentID, Text(ThisRecord.timestamp))
        ),
        ??
    )
)

[RandyHayes]

@ericonline 

I believe what you are looking for is this:

ForAll(
    GroupBy(
        AddColumns(
            TABLE_NAME,
            "cDate", DateTimeValue(timestamp)
        ),
        "equipmentID",
        "_recs"
    ),
    Patch(First(SortByColumns(_recs, "cDate", Descending)), {equipmentID: equipmentID})
) 

I hope this is helpful for you. 

[ericonline]

I will test this now. I was not expecting to see a Patch() here! 

[RandyHayes]

@ericonline 

Patch patches records and returns a record!  In this case, we are patching the equipmentid column back into the grouped record - because when you group by a column, that column is the column in the resulting table, not the grouped records.  So, we're just putting it back in to the record we want.

[ericonline]

@RandyHayes , I understand the formula you proposed, but it looks like I'm hitting delegation.

Example:

SQL Table Rows: ~4500

Function: (as you proposed)

ClearCollect(colLatestRecords,
    ForAll(
        GroupBy(
            AddColumns(
                '[dbo].[TABLE_NAME]',
                "cDate", 
                timestamp
            ),
            "equipmentID",
            "_recs"
        ),
        Patch(
            First(SortByColumns(_recs, "cDate", Descending)), 
            {
                equipmentID: equipmentID
            }
        )
    )
)

Results: in a single record though there are many equipmentID's in the table (equipmentID is null though until ~ID 2500).

- "PowerApps encountered an error" message is because the field is blank (verified).

- I have AdvancedSettings/DataRowLimit at default 500

ID	equipmentID	cdate
500	"Powerapps encountered an error"	2/21/2021 0500

Delegation issue:

So I tried a Filter function on the SQL table like so (timestamp is a DATETIME(2) data type):

Set(varLookback, Now() - 1);

ClearCollect(colLatestRecords,
    ForAll(
        GroupBy(
            AddColumns(
                Filter(
                    '[dbo].[TABLE_NAME]',
                    timestamp > varLookback
                ),
                "cDate", 
                timestamp
            ),
            "equipmentID",
            "_recs"
        ),
        Patch(
            First(SortByColumns(_recs, "cDate", Descending)), 
            {
                equipmentID: equipmentID
            }
        )
    )
)

But this always returns an empty collection. Shouldn't the dates in the timestamp column and varLookback be comparable in this case?

[RandyHayes]

@ericonline 

Yes...the formula I provided is not delegable.  The AddColumns is the issue there as it is not delegable.

However, delegation did not seem like and issue as you originally had Distinct - which is also not delegable.

You are on the right path though...to pre-filter the information going into the AddColumns.

But, your timestamps are going to be a problem to filter on as they are not going to be compatible with the date type you are putting in a variable.

My suggestion is that, since you are on SQL...can you just put together a View in SQL to return the filtered information i.e. last month worth of data?