cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joorge_C Regular Visitor
Regular Visitor

Look up a record from last day which is no longer present in latest load

Hello All!

Im trying to look up closed transactions, the only thing is when they are closed they are no longer existent, so Im trying to figure out a way to look up past records and compare them to latest update of data and see which were closed.

 

thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Look up a record from last day which is no longer present in latest load

@Joorge_C  Please try this as a New Column

 

MissingFlag = 
VAR _LatestDate = MAX(Test221PrevTransMissingCount[LoadDate])
VAR _Check = LOOKUPVALUE(Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[LoadDate],_LatestDate)
RETURN IF(ISBLANK(_Check) && Test221PrevTransMissingCount[LoadDate] <> _LatestDate,1,0)

image.png

 

You can then use the MissingFlag field to sum how many are missing compared to latest loaddate.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





6 REPLIES 6
Super User
Super User

Re: Look up a record from last day which is no longer present in latest load

@Joorge_C  Please provide the sample data and your expected output to suggest you an accurate solution.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Joorge_C Regular Visitor
Regular Visitor

Re: Look up a record from last day which is no longer present in latest load

Hello Thanks... so something like this. Transaction 1 and 2 where closed in Febuary since they are no longer existent.

I would need to count how many where closed on the last loaded date.

 

 

Loaded dateTransactionRow
1/1/20191231
1/1/20192342
1/1/20193453
2/1/20193454
Super User
Super User

Re: Look up a record from last day which is no longer present in latest load

@Joorge_C  To make it clear, the transactions 123 and 234 didn't apper on the latest Loaded Date i.e. 02/01/2019 and you want this count to be 2. Is that correct ? Do you want this as a new column ?



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Joorge_C Regular Visitor
Regular Visitor

Re: Look up a record from last day which is no longer present in latest load

Correct, you are right on!

Super User
Super User

Re: Look up a record from last day which is no longer present in latest load

@Joorge_C  Please try this as a New Column

 

MissingFlag = 
VAR _LatestDate = MAX(Test221PrevTransMissingCount[LoadDate])
VAR _Check = LOOKUPVALUE(Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[LoadDate],_LatestDate)
RETURN IF(ISBLANK(_Check) && Test221PrevTransMissingCount[LoadDate] <> _LatestDate,1,0)

image.png

 

You can then use the MissingFlag field to sum how many are missing compared to latest loaddate.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Joorge_C Regular Visitor
Regular Visitor

Re: Look up a record from last day which is no longer present in latest load

MUCH APPRICIATED!