filtering the date using sql

user095063

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. 

MarkSnodgrass

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

MarkSnodgrass

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:

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

Hope this helps.

MarkSnodgrass

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

user095063

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

 

MarkSnodgrass

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:

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

Hope this helps.

user095063

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

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.

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.