filtering the date using sql

user095063
user095063 Member
in APIs & Domo Developer

Hi, 

 

I have a large dataset that I am trying to filter by date in such way that I only have October month, should be straightforward but somehow it doesn't recognize the date at all. Here is my sql code:

 

SELECT `earnings`, `date`
FROM `amazon_a9`
WHERE `date` BETWEEN "Oct 1, 2019" AND "Oct 31, 2019"
GROUP BY `date`

 

I have also tried the following just to make sure it sees the date

 

SELECT `earnings`, `date`
FROM `amazon_a9`
WHERE `date` = "Oct 25, 2019" 

And it says: "No rows match your criteria" which is impossible as I copy-pasted this from the raw data. What am I missing here? 

 

Thank you for your help in advance. 

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would suggest you make your where clause to be:

    WHERE 'date' BETWEEN '10/1/19' AND '10/31/19'

     

    This is the more standard date format for SQL to evaluate the dates, assuming your date format is m/d/yy

    **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.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    An important thing to note is that in ETL and in SQL, it will not load your entire dataset if you have a large dataset. Therefore, it can be tricky validate your data while building if you are looking for specific values. Depending on the setting, it may just be loading 10k rows of your dataset. Keep that in mind. 

    As far as getting a summary total, in SQL this would sum it up by day:

    SELECT SUM(Earnings), Date

    FROM Amazon_A9

    GROUP BY Date

     

    In ETL, you can do this by dropping in the Group By tile and applying it like this:

    groupby.PNG

    This would also give you the total earnings for each day.

    Hope this helps.

    **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

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would suggest you make your where clause to be:

    WHERE 'date' BETWEEN '10/1/19' AND '10/31/19'

     

    This is the more standard date format for SQL to evaluate the dates, assuming your date format is m/d/yy

    **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.
  • user095063
    user095063 Member

    Hi Mark, I have tried this just now and the same error has appeared. I am adding a screenshot for you to see the data. I also tried just April month as I can clearly see it is there. I was able to filter the data with Magic ETL but ran into another issue where I can't group by date. All I want is to get the sum of the revenue grouped by date.

     

    Your help & time much appreciated,

    Kseniya

     

    Screen Shot 2019-11-06 at 12.21.15 PM.png

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    An important thing to note is that in ETL and in SQL, it will not load your entire dataset if you have a large dataset. Therefore, it can be tricky validate your data while building if you are looking for specific values. Depending on the setting, it may just be loading 10k rows of your dataset. Keep that in mind. 

    As far as getting a summary total, in SQL this would sum it up by day:

    SELECT SUM(Earnings), Date

    FROM Amazon_A9

    GROUP BY Date

     

    In ETL, you can do this by dropping in the Group By tile and applying it like this:

    groupby.PNG

    This would also give you the total earnings for each day.

    Hope this helps.

    **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.
  • user095063
    user095063 Member

    Hey Mark, 

     

    Thank you very much for your help! I apologize for having so many questions but I am new at DOMO and want to understand how to do basic data manipulation. My dataset is indeed very large with hundreds of million rows, is why I want to filter it. But I definitely need the accurate revenue sum - our numbers are not identical from raw data - maybe that's the reason. How can I make sure I will have the correct sum for my grouped data? Do you have any suggestions? Maybe divide the data into chunks. And lastly, where can I change the settings so it shows all the rows? 

     

    Thank you very much for your time and help! 

  • MarkSnodgrass
    MarkSnodgrass Coach

    In the ETL and the SQL dataflow, you can't pull in all rows because it is just for previewing. You can adjust how many rows you see, but you can't choose all rows when your dataset is very large.

    rowpreview.PNG

    You will want to complete your ETL with an output dataset and then build a basic card that is powered by that dataset to validate your numbers. I often add a card in my Overview page when I need to build a card to validate a dataset because no one else will see that card. You should try a few different cards with different filters to help you validate the numbers you are looking for.

    **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.
This discussion has been closed.

Categories