# Return the value based on the most recent matching the criaterion

Options
Member

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

• Coach
Options

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 a`

Here's the screenshot with query and results

Hope that helps get you what you need.

Sincerely,

Valiant_Ronin

**Say "Thanks" by clicking the "heart" in the post that helped you.

• Coach
Options

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 a`

Here's the screenshot with query and results

Hope that helps get you what you need.

Sincerely,

Valiant_Ronin

**Say "Thanks" by clicking the "heart" in the post that helped you.

• Options

Thank you! I'll try. Should I use this in a beastmode?

• Coach
Options

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

• Options

Thanks a lot! It works

• Options

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