I am trying to make a connection to the below Access query via a data extract. This query runs in about a second on Access and returns approx 400,000 records - however Tableau seems to have issues with it - after about 30 minutes of attempting to import data zero records have been returned so far.
The purpose of the quey is to return certain characteristics of loans in a pool - the fields Deal_Name, LoanID and Statu Lag0 are from the current period date for a given LoanID and the fields Status Lag1 and Value are for the same LoanID only from the prior period date where the dates are 1 month apart.
(SELECT TOP 1 Status FROM [Master] AS Dupe WHERE Dupe.LoanID = [Master].LoanID AND Dupe.DATE=dateadd("m",-1, [Master].DATE)) AS [Status Lag1],
[Master].Status AS [Status Lag0],
(SELECT TOP 1 Balance FROM [Master] AS Dupe WHERE Dupe.LoanID = [Master].LoanID AND Dupe.DATE=dateadd("m",-1, [Master].DATE)) AS [Value]
Can anyone see what the issue is or suggest a better way to handle?