Multi ROw COmparison

Question

In the below given table, I wanna compare two records eg. Record 190 and 191 so for each pair I have two historical status in last column, I wanna compare those pairs and then i wanna check that the NO status record's data should be recent than the YES status pack.

I have sorted the data that in Descending order to get YES record first and then the NO record next to it.

RecordIDPack IdPack Indication IdRegulatory Indication IdAssessment Publication DateIs Historical19045167471178073671523-10-2019YES19145167471178073671509-09-2020NO19245167571178273671723-10-2019YES19345167571178273671709-09-2020NO19445167671178473671923-10-2019YES19545167671178473671909-09-2020NO

A quick response would be appreciated

IraWatt · Answer

@Pankaj_G  Another way of doing this is like this:

First, I grouped by the pack ID's as they seen to distinguish each pair of records we want to compare. Then using datetime parse function so I can compare the dates as a date datatype I check if the row below the historical row if its date is less. If yes I identify it with "Bad" else just use the row above.

Any questions or issues please ask :)
HTH!
Ira

Workflow.yxmd

Emmanuel_G · Answer

Hi @Pankaj_G ,

Do you want something like this ?

Compare N and N-1 day by pair.yxmd

IraWatt · Answer

Hey @Pankaj_G,

When you say you want to compare them, what kind of output are you looking for? Do you need a column to say which columns are different between 191 and 190? Could you show your expected result?

Thanks,

Ira