filtering the date using sql

Reply
Highlighted
Yellow Belt

filtering the date using sql

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. 


Accepted Solutions
Major Blue Belt

Re: filtering the date using sql

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Major Blue Belt

Re: filtering the date using sql

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Major Blue Belt

Re: filtering the date using sql

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Yellow Belt

Re: filtering the date using sql

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

Major Blue Belt

Re: filtering the date using sql

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Yellow Belt

Re: filtering the date using sql

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! 

Major Blue Belt

Re: filtering the date using sql

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!