cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
elliotdixon Member
Member

Lookup in related table and filter by value

Hi

Hopefully you folks can help out with this as I am stuck.

 

I have two tables that are linked by a Run_ID

The activity table shows the status changes of runs and I want to know the difference between what is actually happening and what was planned.

 

I want to know the difference in time between JobRun[RunPickupTime] and Activity[ActivityTime] where Activity[RunStatus]=4

 

As there may be duplicate rows where the Activity[RunStatus] is the same I need to also limit it to show the time with the lowest Activity[ActivityID]

 

TableJobRun
JobRun[Run_ID]JobRun[RunPickupTime]
2584502/05/2018 7:40

 

TableActivity  
Activity[Activity_Run_ID]Activity[ActivityTime]Activity[RunStatus]Activity[ActivityID]
2584502/05/2018 6:3312278
2584502/05/2018 10:3942387
2584502/05/2018 10:4042498
2584502/05/2018 10:4053123

 

Cheers in advance.

ED

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Lookup in related table and filter by value

Try this calculated column out (in JobRun):

 

Column = 
VAR tmpTable = FILTER(RELATEDTABLE(Activity),[RunStatus]=4)
VAR minActivityID = MINX(tmpTable,[ActivityID])
VAR tmpTable1 = FILTER(tmpTable,[ActivityID]=minActivityID)
RETURN DATEDIFF([RunPickupTime],MAXX(tmpTable1,[ActivityTime]),MINUTE)

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

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: Lookup in related table and filter by value

Try this calculated column out (in JobRun):

 

Column = 
VAR tmpTable = FILTER(RELATEDTABLE(Activity),[RunStatus]=4)
VAR minActivityID = MINX(tmpTable,[ActivityID])
VAR tmpTable1 = FILTER(tmpTable,[ActivityID]=minActivityID)
RETURN DATEDIFF([RunPickupTime],MAXX(tmpTable1,[ActivityTime]),MINUTE)

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

Proud to be a Datanaut!


elliotdixon Member
Member

Re: Lookup in related table and filter by value

This worked great! Thanks so much @Greg_Deckler for the solution. First time I have used VAR as well. Learning time.

 

Cheers.