Return the value based on the most recent matching the criaterion

user07693

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 

Valiant

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

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

Valiant

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

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

Unknown

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

Valiant

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

Unknown

Thanks a lot! It works

Unknown

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