Return the value based on the most recent matching the criaterion
Hi all, please help to resolve the following issue.
I Have a very huge dataset showing the following and I need to fill the column "Currency 2/ USD" with values:
# Time; Cur 1; Cur 2; Pair; Price; Currency 2/USD
1 2:00 pm EUR ; USD; EUR/USD 1,165 1
2 5:00 pm EUR ; USD; EUR/USD 1,167 1
3 5:01 pm UAH ; EUR; UAH/EUR 0,032 1,167
4 5:02 pm UAH ; EUR; UAH/EUR 0,031 1,167
5 5:03 pm EUR ; USD; EUR/USD 1,169 1
6 5:06 pm UAH ; EUR; UAH/EUR 0,031 1,169
So the logic of filling the column "Currency 2 / USD" is the following: If Currency 2 is USD then return 1, if it is not USD (let's say EUR like in line 3), look for the most recent this currency / USD price and state it (1,167 for lines 3 and 4 as the most recent EUR/USD is line 2 with price 1,167).
How should I do this? P.S. I have 67 unique pairs
Best Answer
-
That was a fun one. Here's the query I used:
SELECT a.* ,
CASE WHEN a.`Currency 2` = 'USD' THEN 1
ELSE (SELECT b.`Price`
FROM `currency_data` AS b
WHERE a.`Currency 2` = LEFT(b.`Pair`,3)
AND RIGHT(b.`Pair`,3) = 'USD'
AND b.`Time` <= a.`Time`
ORDER BY b.`Time` DESC
LIMIT 1)
END AS 'Currency 2/USD'
FROM `currency_data` AS aHere's the screenshot with query and results
Hope that helps get you what you need.
Sincerely,
Valiant_Ronin
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0
Answers
-
That was a fun one. Here's the query I used:
SELECT a.* ,
CASE WHEN a.`Currency 2` = 'USD' THEN 1
ELSE (SELECT b.`Price`
FROM `currency_data` AS b
WHERE a.`Currency 2` = LEFT(b.`Pair`,3)
AND RIGHT(b.`Pair`,3) = 'USD'
AND b.`Time` <= a.`Time`
ORDER BY b.`Time` DESC
LIMIT 1)
END AS 'Currency 2/USD'
FROM `currency_data` AS aHere's the screenshot with query and results
Hope that helps get you what you need.
Sincerely,
Valiant_Ronin
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0 -
Thank you! I'll try. Should I use this in a beastmode?
0 -
No, you'll need to do this with a SQL Data Transform.
You're having to compare the dataset against itself so you can't use only card level beastmodes.
Once you open the SQL transform, select your dataset and you should be able to then tweak my code to match column/dataset names.
Let me know if you need further assistance,
Valiant_Ronin
0 -
Thanks a lot! It works
1 -
Hi again,
I faced the problem that DOMO can't handle this request as I have like 60 million rows of initial data and after a day of processing DOMO just refuses to do that
any thoughts how to optimize? Got the response from DOMO that is huge indeed and SQL is not good for such an exercise
0
Categories
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive