Return the value based on the most recent matching the criaterion

user07693
user07693
in Magic ETL

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

  • Valiant
    Valiant Coach
    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 a

    Here's the screenshot with query and results

    image.png

     

    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.

Answers

  • Valiant
    Valiant Coach
    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 a

    Here's the screenshot with query and results

    image.png

     

    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
    Unknown

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

  • Valiant
    Valiant Coach

    No, you'll need to do this with a SQL Data Transform.

    image.png

    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
    Unknown

    Thanks a lot! It works

  • Unknown
    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

Categories