Putting Current day and Previous day as columns instead of rows

DANIH
DANIH
in Magic ETL

Hello!

I have run into a problem just like this link:

https://dojo.domo.com/discussion/14086/current-day-vs-previous-day-in-table


I followed the sql transform but once I ran it the output dataset was the same as the input dataset. I don't know what I am doing wrong, I even uploaded a file that is exact to the example in the link above.

If someone can help me figure out why it seems like its not working for me?


Thanks!

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited August 2021 Answer ✓

    @DANIH your final output should be SELECT * FROM transform2, not from the original input dataset. Not sure if that was just a typo, but your transform SQL looks correct that leads up to the final output.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @DANIH

    Can you put your MySQL code here for us to review?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • DANIH
    DANIH
    edited August 2021

    @GrantSmith

    1st transform:

    SELECT 


     `Date` 

    ,SUM(`Sales`) As 'Sales'  

    ,`Product`


    FROM `domo_test_csv`


    GROUP BY `Date`, `Product`


    2nd Transform:

    SELECT 


     a.`Date`

    ,a.`Sales` AS 'Current_Day'

    ,b.`Sales` As 'Previous_Day'

    ,a.`Product`


    FROM `domo_test_csv` a


    LEFT JOIN `domo_test_csv` b

    ON a.`Date` = DATE_ADD(b.`Date`, INTERVAL 1 DAY)

    AND a.`Product` = b. Product


    output data set:

    SELECT * FROM `domo_test_csv`

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited August 2021 Answer ✓

    @DANIH your final output should be SELECT * FROM transform2, not from the original input dataset. Not sure if that was just a typo, but your transform SQL looks correct that leads up to the final output.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories