Dataset Views - JOIN on date range?

JunkDoom

In most database systems, you can create a join/view on date ranges like so:

SELECT * FROM A1 JOIN A2
ON A1.ProductID = A2.ProductID
AND (A2.SalesDate >= A1.StartDate)
AND (A2.SalesDate <= A1.EndDate OR A1.EndDate IS NULL)

However, a DataSet View in Domo only seems to allow joins on exact values and does not allow ranges like this. I hate to do ETLs and take up storage since the tables in my case have tens of millions of rows. Is there any way to do this in a DataSet view?

MichelleH

@JunkDoom I don't believe there is an easy way to do this in Dataset Views since they are best used for simple transformations. If you are concerned about materializing a large amount of rows, is there a way you could narrow down the amount of data in your ETL output, like a rolling date range?

MarkSnodgrass

I'm not sure if it is available in Beta yet or not, but I remember hearing about a SQL Editor for Views, which should address this issue for you. I would reach out to your CSM and see if they can provide any insight into that feature. They may be able to let you be a Beta user of the feature.

MichelleH

@JunkDoom I don't believe there is an easy way to do this in Dataset Views since they are best used for simple transformations. If you are concerned about materializing a large amount of rows, is there a way you could narrow down the amount of data in your ETL output, like a rolling date range?

MarkSnodgrass

I'm not sure if it is available in Beta yet or not, but I remember hearing about a SQL Editor for Views, which should address this issue for you. I would reach out to your CSM and see if they can provide any insight into that feature. They may be able to let you be a Beta user of the feature.