For the termdate column error, run the following 4 queries and it'll work: UPDATE hr SET termdate = IF(termdate IS NOT NULL AND termdate != '', date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00') WHERE true; SELECT termdate from hr; SET sql_mode = 'ALLOW_INVALID_DATES'; ALTER TABLE hr MODIFY COLUMN termdate DATE;
To update age = '2060-01-01' AND birthdate < '2070-01-01'; The reason age is showing in negative is instead of 1960 it is mentioned as 2060 , so you can use DATE_SUB to subtract 100 years. Hope this helps thanks !
@@herdataproject Wonderful news, I can't wait to follow them, I am currently doing an SQL course, as soon as I am done, I want to replicate this project to improve my skills.
You made it already!! Thank you so much!!! Extremely helpful!!! Keep up the videos plz your projects are unbelievably helpful for me right now as I’m work my on building a strong portfolio, I am sure you are helping many others also!
Thanks, Irene I was able to go through the tutorial and actually practice on it and I can say it is absolutely helpful to me as a beginner in the data industry. Thumbs up and keep up inspiring!! love from Bangladesh
I want to say thank you for this video! It made it using Microsoft SQL server much more understandable because I understand that it is more advanced but using my SQL workbench requires a more hands-on approach to learning the basics. You showed how to manually change things in the best way possible! Also, I will support any future videos !! Keep up the good work!
@@herdataproject Thank you for much for always ..However I got stuck trying to answer the first question ...It keeps returning an error message 'Incorrect DATE value:'0000-00-00'.. Here is my line of code ; Select gender, count(*) AS gender_count FROM hr WHERE age >= 18 AND termdate = '0000-00-00' GROUP BY gender; ..Please where am I getting it wrong ?
PS - I was having issues with obtaining the data set, when I open the file that you've suggested excel doesn't read it as a table and there is an error report. I was able to find the same data set on kaggle (the data set did not include the negative age for one of the employees as shown in your video) and I was able to follow along thereafter.
You're most welcome. I'm glad you found the video helpful. Sorry for the hustle in getting the dataset. It's on Github and Telegram. So once you join Telegram, check the pinned messages and you'll be able to get the dataset
Kudos, Great video! I thoroughly enjoyed it and found the SQL Data Analysis walkthrough to be quite accessible, especially on date formatting and conversion 👏🏽. However, for enhanced practicality, consider leveraging Power BI's DAX to calculate most measures after establishing a data model. This approach enhances interactivity and reusability in any project. Looking forward to more content like this! 👏🏽👋🏽💯
Thanks for the insights. I'm glad you found the video helpful. I'll be sure to show how to use DAX in the next PowerBI project. Stay tuned and thanks for watching!!
Thank you so much for this wonderful tutorial, you are so calm in teaching and it makes it very understanding. Please can you do more videos on SQL+Power Bi. I just started my learning journey as a Data Analyst. Please can you Mentor me
It's awesome!! I've watched n number of TH-cam videos but i found this video is very useful especially at data cleaning and data analysis part as its crystal clear in terms of better understanding... Thanks so much ❤👍🤝.
Q6. How does the gender distribution vary across departments and JOB TITLES? 43:18 I think you ignored the "job title' part of this question. This was what I did. SELECT * FROM hr; SELECT department, jobtitle, gender, count(*) count FROM hr WHERE age >= 18 AND termdate = '0000-00-00' GROUP BY department, jobtitle, gender ORDER BY department;
Heeeey, yes I did. I think it was coz of how varied they were so decided to just go with departments to have a general overview. Thanks for sharing your solution and keep up the amazing work!!
Thank you for this video. It was really easy to follow and understand for me. And when I got stuck with a sql queries related with termdate, I could find solution in the comment as well..
The assumption is that termdate is the date the employment ended. So it wouldn't make sense to have it as a future date. That's why I made sure to use the termdate as less than more equal to the current date. Hope this helps!
Hello first and foremost thank you Irene for showing this project,as i was going through it using sql server microsoft i am stuck on changing the termdate data type anyone using ssms how did you go about it,i will appreciate for any help
HEllo, for the termdate column, I don't have the 0000-00-00 when the line is empty, I have nothing so I have an error when I want to convert the type text to a date.
There's another alternative you can use to convert the values from text to date UPDATE hr SET termdate = IF(termdate = '', NULL, str_to_date(termdate, "%Y-%m-%d %H:%i:%s UTC")) WHERE termdate IS NOT NULL; And in place of termdate = '0000-00-00 ' use IS NULL. Let me know if it works for you
Bro please tell me update command not working in my SQL workbench since last 24 hours😭😭😭 I tried again and again Pls tell me where is fault, chatGPT speaks I apologise too
SELECT birthdate FROM hr; SET sql_safe_updates = 0; UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d') ELSE NULL END; 03:00:08 UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d') ELSE NULL END Error Code: 1292. Truncated incorrect date value: '06/04/1991' 0.000 sec please could you help me with this
First import the dataset again because the values have already become NULL and then run SET SQL_MODE = ' '; then run the update query. Hopefully it works
Hi, thank you for helping me learn with this video, but I had a doubt, some of the answers I get are slightly different from yours for example I got slightly different values when I calculated the different age groups, could you please tell me why is that?
Hello 👋 ! I was trying this in Power Query Editor for the data cleaning part for hire_date col. How can the null values be dealt with in this way, can you please help😅
Hello, try this. Hard to explain via text but hope it works Select the table or range that contains the column with null values. Go to the "Data" tab. Click on "From Table/Range" to open the Power Query Editor. In the Power Query Editor, find and select the column with null values in the "Query Settings" pane on the right. Go to the "Transform" tab in the Power Query Editor ribbon. Click on "Replace Values" from the "Any Column" group. In the "Replace Values" dialog box, enter "null" (without quotes) in the "Value to Find" box. Leave the "Replace With" box empty to remove null values from the column. Click "OK" to apply the changes. Once you've made all necessary transformations, click "Close & Apply"
Heloo Mam, why we need to analyse these data in sql instead of excel? we didn't connect that sql analysis in power bi then why we need to find that analyse in sql just simply use one tool like power bi and easy find that analyse in power bi .what the use in that analyse in sql? I don't know why please leave the answer mam🙏
It's just a preference. There are people who had requested to be shown how to clean and analyse data using SQL. You can use Excel or PowerBI from start to finish if that's what you prefer.
This is great, just a question, is the dataset you provided via GitHub the exact same as the one you used in the video? I noticed the values I'm getting in my analysis as I follow along are slightly different from yours, I'm worried i might be making mistakes in my queries.. I'm using SQL server by the way, the differences are minor but they do affect the results of the analysis for example for you, the lowest turnover rate was the marketing department and mine was the business development department.
thanks for the video, very well explained, just one question if the original CSV file that you initially imported to SQL suffers any change lets say gets more rows added whitin the already existing categories would that get reflected on the MySQL values as well?
Hello, I skipped the data modelling section because the data was already cleaned in SQL and didn't require advanced transformations like creating measures or calculated columns. But you could do the modelling if you're looking to make you report interactive
why did you past all the queries in diff excel files & then imported in power bi, instead of you can directly connect to power bi using import mode....
Hi, is this the correct way of utilizing databases for Power BI Visualizations? I mean we need to export the details as csv file then import it to Power BI? Or can we directly connect to database to get the data? Thanks
Hello, it's okay if they remain blank. Mine are updating to '0000-00-00' because strict mode in my version of SQL has been disabled. Yours is not that's why they're not updating. The only thing you'll change in your queries while answering the questions is just writing 'termdate IS NULL' instead of 'termdate = '0000-00-00' But if you'd also like to disable strict mode on your version of SQL you can run this query SET SQL_MODE = ' '; Then run your queries. Let me know if it works!
@@herdataproject thank you soooo much mam for relying I will try it .. Thank you for being sooo kind 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻.. thank you for giving your valuable time. 🙏🏻🙏🏻🙏🏻
You're welcome. I'm not yet an expert but to be good you need to learn and always practise what you've learnt so that you don't forget. That way you build knowledge on a lot of concepts.
i get trouble with this query, the output is error : SELECT GENDER, count(*) AS COUNT FROM HR WHERE AGE >= 18 AND TERMDATE = IS NOT NULL group by GENDER; because some part in termdate table is null? how can fix it?
can anyone explain the aim of this project mean why we make dishbord and i cant understand dishbord mean wht is the problem behind this that we make this typle dishbord
Hi there, I am getting an error on question 5. This is my code: SELECT AVG(DATEDIFF(termdate, hire_date))/365 AS average_length_of_employment FROM hr WHERE termdate =18; This is the error I am receiving: Error Code: 1525. Incorrect DATE value: '0000-00-00'. Any assistance would be appreciated. Thanx
Hi the link works. You can try it again here t.me/+7ms4DzzAYVlhYmZk If it doesn't work, try downloading from Github github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI
thank you for the video you created, it's an awesome project and the way you explain it makes the code syntax easier to understand. anyway, may i add this project to my resume?
Thank you, and thanks for watching. Yes you can for now but I'd advise finding your own dataset and working on it so that you can make sure you have mastered the concepts as well as the entire process. And also in case a recruiter came across this video they don't assume you just did copy and paste.
@@herdataproject thank you again madam. I'll write your name on the description and perhaps the link to the video. After completing this project, i will start to build my own one.
Hi, it was a great video. I'm working on a HR Analytics Portfolio Project. The way You imported data in Power BI, can you please tell me how make this Dashboard Interactive ?
if everyone has trouble in the termdate column error when you update , let take the code " SET SQL_SAFE_UPDATES = 0" before the code update this colums. like this: SET SQL_SAFE_UPDATES = 0; UPDATE hr SET termdate = IF(termdate IS NOT NULL AND termdate!='',date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00') WHERE TRUE ;
Hello, how to solve the below problem in Microsoft sql UPDATE HR SET termdate = CONVERT(date, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' '; Error - Conversion failed when converting date and/or time from character string.
you can modify the UPDATE statement to exclude the problematic rows that cannot be converted to a date. UPDATE HR SET termdate = CONVERT(date, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' ' AND ISDATE(termdate) = 1; The additional condition ISDATE(termdate) = 1 is added to ensure that only valid date values are considered for conversion. The ISDATE function checks if the value can be converted to a valid date, and only rows where the 'termdate' value is a valid date will be updated. Hope this helps
I couldnt update termdate using PLSQL developer. STR_TO_DATE is not working. I tried using TO_CHAR(TERMDATE,'DD/MM/YYYY') , trunc(TERMDATE).. nothing works out. Datatype is Varchar 26. Plz help.
I fixed it.. following syntax works out.. UPDATE tablename SET TERMDATE=cast(to_timestamp_tz(TERMDATE, 'yyyy-mm-dd hh24:mi:ss "UTC"') as date) WHERE TERMDATE IS NOT NULL;
Irene, First I tried to download CSV, it became a text file, when I changed the file to CSV and imported to SQL, the data is not clear and has errors, please tell me step by step how to download the file from your GITHUB, and how to open in MySQL
You can also download it from the telegram if you're able to install the app. I think it should download as a CSV file. Once you join the group it'll be pinned at the top
@@herdataproject Update command on birthday column, he reflects error like truncated incorrect date value 😭😭 I tired since 3 days not proceeding further 🙏
when we are considering the term date 00000 to select the employees, what about the employees whose term date is in future?that means they are still in the company and we are not considering them
Hi, for min and max ages did anyone not get negative values? My result doesn't show -42. It shows minimum value as 20. I tried copy pasting the query from github too. Same result. Would really appreciate any help:) Thank you for the video .
Depends on the column you're referring to. For dates etc, you're running the code on a different date than I did. If that's not the case then please share a screenshot in the Telegram group to allow me to assist you further
Please i have a question... When i query for "what is the gender breakdown of employee in the company" and SQL returns the result table.....is there a way to save the result so that when I connect powerBI to the SQL database I will have access to the result table from the query rather than exporting each result as a CSV because imagine you are working with a large company and the result table from your query is 3million rows I don't think I will be able to export that into excel easily? Do you get me?
Yes I get you. I think what you're looking for is something like a database view. Create a database view with the SQL query logic. Power BI can then directly connect to this view and retrieve data without running the query every time. I have never tried to use it before with PowerBI or even Tableau but I think it would work. You can try it out and let me know.
The dataset can be downloaded from Telegram or GitHub. On Telegram check the pinned message Telegram - t.me/+7ms4DzzAYVlhYmZk GitHub - github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI Please leave any questions or requests in the comments
Using Microsoft sql and there is no str_to_date function, tried changing termdate data type but was not able to, tried convert, cast but it's not working, please help
In Microsoft SQL Server, you can use the CONVERT() function to convert a string to a date. The format string to use with CONVERT() is different from the one used with STR_TO_DATE() in MySQL. UPDATE hr SET termdate = CONVERT(DATETIME, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' ' In this example, the CONVERT() function is used to convert the termdate string to a datetime data type using the format code 120, which corresponds to the ISO 8601 format (yyyy-mm-dd hh:mi:ss(24h)). Something to keep in mind is that the CONVERT() function can be affected by the default language setting of the SQL Server instance. If you're experiencing issues with the conversion, you can try using the SET LANGUAGE statement to set the language explicitly before running the UPDATE statement: SET LANGUAGE us_english UPDATE hr SET termdate = CONVERT(DATETIME, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' ' I hope this helps : )
967 records showing age minus value due to date conversion, some dates in text value like 6/26/67 changed to 2067-6-26 instead of 1967. I'm interested in, how to change these data to correct 1900 dates in the same command? anybody? --- UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(str_to_date(birthdate, '%m/%d/%Y'), '%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(str_to_date(birthdate, '%m-%d-%Y'), '%Y-%m-%d') ELSE NULL END; --- And great tutorial btw, really helpful, two thumbs up.
Thank you so much. More to come😊 So I don't think I would be accurate to convert those dates to 1967 and so on because we don't know for sure that is the value that is supposed to be there. But if you decide to do that then you would have to mention it in your final documentation.
They don't have to update to 0000-00-00. It all depends with the settings of your MySQL. If yours have remained as NULL it's okay. When writing the queries use WHERE termdate IS NULL instead of WHERE termdate = '0000-00-00'
Hey, i keep getting this error, can u tell me how will i be able to resolve this issues for following code: UPDATE hr SET birthdate=CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate,'%m/%d/%y'),'%y-%m=%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate,'%m/%d/%y'),'%y-%m=%d') ELSE NULL END Error Code: 1411. Incorrect datetime value: '06-04-1991' for function str_to_date 0.000 sec
17:19:17 UPDATE hr SET birthdate=CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate,'%m/%d/%Y'),'%Y-%m-%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate,'%m-%d-%Y'),'%Y-%m-%d') ELSE NULL END Error Code: 1411. Incorrect datetime value: '2012-01-01' for function str_to_date 0.012 sec Could you help me with this?
For the termdate column error, run the following 4 queries and it'll work:
UPDATE hr
SET termdate = IF(termdate IS NOT NULL AND termdate != '', date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00')
WHERE true;
SELECT termdate from hr;
SET sql_mode = 'ALLOW_INVALID_DATES';
ALTER TABLE hr
MODIFY COLUMN termdate DATE;
It worked , Thanks 🤝
Nice, thanks!
I stucked at this error and just found your precious comment, Thank you
thanks man
Can this work in Microsoft SQL server management studio pls?
This one is my first power bi report and this report will remain special forever in my life.
To update age = '2060-01-01' AND birthdate < '2070-01-01';
The reason age is showing in negative is instead of 1960 it is mentioned as 2060 , so you can use DATE_SUB to subtract 100 years.
Hope this helps thanks !
Thank you!
thanks mate ... very helpful...
Irene, amazing work, quite impressive, thank you for supporting the community with your knowledge
Thanks Simon. More projects coming soon
@@herdataproject Wonderful news, I can't wait to follow them, I am currently doing an SQL course, as soon as I am done, I want to replicate this project to improve my skills.
You made it already!! Thank you so much!!! Extremely helpful!!! Keep up the videos plz your projects are unbelievably helpful for me right now as I’m work my on building a strong portfolio, I am sure you are helping many others also!
You're welcome. More videos coming soon. Thanks for watching!
I’m so glad I found your channel! I really like learning through projects, will you be adding on more videos? Thanks so much!
You're welcome. Yes there'll be more videos soon. Stay tuned!
Thanks, Irene I was able to go through the tutorial and actually practice on it and I can say it is absolutely helpful to me as a beginner in the data industry. Thumbs up and keep up inspiring!! love from Bangladesh
I want to say thank you for this video! It made it using Microsoft SQL server much more understandable because I understand that it is more advanced but using my SQL workbench requires a more hands-on approach to learning the basics. You showed how to manually change things in the best way possible! Also, I will support any future videos !! Keep up the good work!
You're most welcome! I really appreciate the feedback and support
@@herdataproject Thank you for much for always ..However I got stuck trying to answer the first question ...It keeps returning an error message 'Incorrect DATE value:'0000-00-00'.. Here is my line of code ; Select gender, count(*) AS gender_count FROM hr WHERE age >= 18 AND termdate = '0000-00-00' GROUP BY gender; ..Please where am I getting it wrong ?
You're welcome and thank you😊
What are the values of your term_date column?
@@herdataproject They are in this format Ma, 0000-00-00, 2014-02-24 etc....
Thanks for covering various scenarios using sql.. quite helpful
You're welcome. I hope you find the most recent project helpful as well
Thanks from Viet Nam. More other videos please. Hope to see them soon.
You're welcome. More to come!
I just bumped into this amazing and well explained tutorial. Thanks so so much...
You're welcome!! Thanks for watching
Excellent tutorial. Many thanks. Can you please make similar projects using intermediate and advanced SQL concepts?
You're welcome. I will definitely do that
Thank you!!!
This helped me soooo much with my own portfolio project.
You're doing great!
PS - I was having issues with obtaining the data set, when I open the file that you've suggested excel doesn't read it as a table and there is an error report. I was able to find the same data set on kaggle (the data set did not include the negative age for one of the employees as shown in your video) and I was able to follow along thereafter.
You're most welcome. I'm glad you found the video helpful. Sorry for the hustle in getting the dataset. It's on Github and Telegram. So once you join Telegram, check the pinned messages and you'll be able to get the dataset
Kudos, Great video! I thoroughly enjoyed it and found the SQL Data Analysis walkthrough to be quite accessible, especially on date formatting and conversion 👏🏽. However, for enhanced practicality, consider leveraging Power BI's DAX to calculate most measures after establishing a data model. This approach enhances interactivity and reusability in any project. Looking forward to more content like this! 👏🏽👋🏽💯
Thanks for the insights. I'm glad you found the video helpful. I'll be sure to show how to use DAX in the next PowerBI project. Stay tuned and thanks for watching!!
@@herdataproject; When you begin the Video, what Application or program is that?? Is it Power BI, or SQL.
@anymjohnson1729 it's MySQL Workbench
Thank you so much for this wonderful tutorial, you are so calm in teaching and it makes it very understanding. Please can you do more videos on SQL+Power Bi.
I just started my learning journey as a Data Analyst. Please can you Mentor me
It's awesome!! I've watched n number of TH-cam videos but i found this video is very useful especially at data cleaning and data analysis part as its crystal clear in terms of better understanding... Thanks so much ❤👍🤝.
Thank you for this video, truly. I learned so much from this video.
You’re most welcome!
I’m so happy to hear that
Q6. How does the gender distribution vary across departments and JOB TITLES?
43:18
I think you ignored the "job title' part of this question. This was what I did.
SELECT * FROM hr;
SELECT department, jobtitle, gender, count(*) count
FROM hr
WHERE age >= 18 AND termdate = '0000-00-00'
GROUP BY department, jobtitle, gender
ORDER BY department;
Heeeey, yes I did. I think it was coz of how varied they were so decided to just go with departments to have a general overview. Thanks for sharing your solution and keep up the amazing work!!
Amazing video! Wow. Wish I wouldve found it sooner. Wouldve saved me so much trouble
Thank you so much!!
very helpful, thankyou!!! will wait for your next update👍
Thank you, more coming soon!!
Clear explanations....thank you so much
You're most welcome!!
Thank you so much for this project🙏It was very clear and easy to follow😊Plz keep making these type of videos👍
You're most welcome.
There's more to come!
Thanks for watching
Need more industry level power bi projects end to end please.. I will pay also if required
Coming soon!!
good suggestion. I would love to see near world data projects with various messy data sources. Would support with donation too!
thank you so much. I can adapt this project to make my project and learn to familiarize with data cleaning and analysis process.
You're welcome. Yes you can and keep practicing to master all the concepts
Amazing work. Plz make Python data Analysis Project Video.
Thank you. Coming soon!
You did a very good job. Keep it up.
Thanks Daniel. I'm glad you found it helpful
Thank you for this video. It was really easy to follow and understand for me. And when I got stuck with a sql queries related with termdate, I could find solution in the comment as well..
You're most welcome. I'm glad you found it helpful
Superb understanding easy way
I cannot thank you enough for this informative video
You are doing it great, keep going,,
Thank you!!
Great Video. i'm confused why termdate
The assumption is that termdate is the date the employment ended. So it wouldn't make sense to have it as a future date. That's why I made sure to use the termdate as less than more equal to the current date. Hope this helps!
Great Work🙌
Thank you for such an amazing video full of information and knowledge 😊👍
You're most welcome!
More to come
@@herdataproject waiting for next
Thank you very much, i found this tutorial very usefull
You're welcome!!
Thank you for your great tutorial. Keep it up!
27:19 , I am getting count as zero n all results as zero
Hello first and foremost thank you Irene for showing this project,as i was going through it using sql server microsoft i am stuck on changing the termdate data type anyone using ssms how did you go about it,i will appreciate for any help
Please can I see all the data types for your columns when trying to upload the data into table.
wow great stuff so much value
Thank you!
HEllo, for the termdate column, I don't have the 0000-00-00 when the line is empty, I have nothing so I have an error when I want to convert the type text to a date.
There's another alternative you can use to convert the values from text to date
UPDATE hr SET termdate = IF(termdate = '', NULL, str_to_date(termdate, "%Y-%m-%d %H:%i:%s UTC")) WHERE termdate IS NOT NULL;
And in place of termdate = '0000-00-00 ' use IS NULL.
Let me know if it works for you
Please, how did u get the Total count, Terminated count cause, hires, and termination cause it tells me the column doesn't exist
Those are derived columns from the inner query which will be run first
Hi 1292 truncated date error value…how to fix that sir ..while doing the query changing string to date format
Bro please tell me update command not working in my SQL workbench since last 24 hours😭😭😭 I tried again and again
Pls tell me where is fault, chatGPT speaks I apologise too
SELECT birthdate FROM hr;
SET sql_safe_updates = 0;
UPDATE hr
SET birthdate = CASE
WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d')
WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d')
ELSE NULL
END;
03:00:08 UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d') ELSE NULL END Error Code: 1292. Truncated incorrect date value: '06/04/1991' 0.000 sec
please could you help me with this
First import the dataset again because the values have already become NULL and then run SET SQL_MODE = ' '; then run the update query.
Hopefully it works
@@herdataproject birthdate becomes null
Yes null
@@esthernwaogu592 pls have you been able to solve this issue?
Hi, thank you for helping me learn with this video, but I had a doubt, some of the answers I get are slightly different from yours for example I got slightly different values when I calculated the different age groups, could you please tell me why is that?
I meant for Question 3 in analysis part
i am not able
import file into postgre sql it showing hiredate is out of range
Amazing work
Thank you
Thank you and you're most welcome!!
Hi, How can I apply filter on this csv files imported through sql query? i am unable to do.
Thanks! It's been helpful. :)
You're welcome!
Thanks for watching: )
Hello 👋 ! I was trying this in Power Query Editor for the data cleaning part for hire_date col. How can the null values be dealt with in this way, can you please help😅
Hello, try this. Hard to explain via text but hope it works
Select the table or range that contains the column with null values.
Go to the "Data" tab.
Click on "From Table/Range" to open the Power Query Editor.
In the Power Query Editor, find and select the column with null values in the "Query Settings" pane on the right.
Go to the "Transform" tab in the Power Query Editor ribbon.
Click on "Replace Values" from the "Any Column" group.
In the "Replace Values" dialog box, enter "null" (without quotes) in the "Value to Find" box.
Leave the "Replace With" box empty to remove null values from the column.
Click "OK" to apply the changes.
Once you've made all necessary transformations, click "Close & Apply"
Heloo Mam, why we need to analyse these data in sql instead of excel?
we didn't connect that sql analysis in power bi then why we need to find that analyse in sql just simply use one tool like power bi and easy find that analyse in power bi .what the use in that analyse in sql?
I don't know why please leave the answer mam🙏
It's just a preference. There are people who had requested to be shown how to clean and analyse data using SQL. You can use Excel or PowerBI from start to finish if that's what you prefer.
@@herdataproject Thank You very much mam👍Thanks for your reply mam✨
Can I use Ms SQL?
Yes you can
Big thanks to you!!!
This is great, just a question, is the dataset you provided via GitHub the exact same as the one you used in the video? I noticed the values I'm getting in my analysis as I follow along are slightly different from yours, I'm worried i might be making mistakes in my queries.. I'm using SQL server by the way, the differences are minor but they do affect the results of the analysis for example for you, the lowest turnover rate was the marketing department and mine was the business development department.
I’ve got the same problem as you😅
thanks for the video, very well explained, just one question if the original CSV file that you initially imported to SQL suffers any change lets say gets more rows added whitin the already existing categories would that get reflected on the MySQL values as well?
I'm not entirely sure what you mean. Are the rows added during the import?
Search for duplicates
Thank you for the video. So you prefer skipping the data modeling and DAX at all?
Hello, I skipped the data modelling section because the data was already cleaned in SQL and didn't require advanced transformations like creating measures or calculated columns. But you could do the modelling if you're looking to make you report interactive
I tried converting the birthdate format but it's not working for me can anyone help
please make vedio on orderback prediction and inventary management.
Noted!! I have added it to my list of projects
Keep going. Nice!
Thank you!!
When I export to PDF, the page gets zoomed in and the export cuts out a part of the right and bottom of the viz. How do I aviod this please?
Hi, you can try checking your zoom before exporting and also the orientation. It should be landscape instead of portrait
Mamm the 10th question it shows me error
To
Termination invalid filed from HR
Kindly share the whole error as is
why did you past all the queries in diff excel files & then imported in power bi, instead of you can directly connect to power bi using import mode....
I'm sure that works too, I think it's a matter of preference
Hi, is this the correct way of utilizing databases for Power BI Visualizations? I mean we need to export the details as csv file then import it to Power BI? Or can we directly connect to database to get the data? Thanks
Hi, it's not the only way. You can also connect directly to your SQL database. Check Microsoft Learn website for more details.
very informative
thanks
You're welcome!!
hi, thanks so much for this great video , i downloaded the dataset but it's cleaned how i can get the uncleaned one please
You're most welcome. You can download it from the Telegram group. I've pinned the original dataset
19:12
While using the query that you have used, the blank cells are not getting updated with '0000-00-00' ..
What shall I do... Please reply..
Hello, it's okay if they remain blank. Mine are updating to '0000-00-00' because strict mode in my version of SQL has been disabled. Yours is not that's why they're not updating. The only thing you'll change in your queries while answering the questions is just writing 'termdate IS NULL' instead of 'termdate = '0000-00-00'
But if you'd also like to disable strict mode on your version of SQL you can run this query
SET SQL_MODE = ' ';
Then run your queries.
Let me know if it works!
@@herdataproject thank you soooo much mam for relying I will try it
..
Thank you for being sooo kind 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻.. thank you for giving your valuable time.
🙏🏻🙏🏻🙏🏻
@@herdataproject how you are soo expert in sql queries,, please please give some suggestions to me,, how can I be good SQL queries..
You're welcome. I'm not yet an expert but to be good you need to learn and always practise what you've learnt so that you don't forget. That way you build knowledge on a lot of concepts.
@@herdataproject thank you mam 🙏🏻🙏🏻
Thanks it working
why it is showing Error code : 1193. unknown system variable 'Age' , even after successful implementation of Age column ?
I'm not sure what the issue could be. You could post the screenshot of the query and the error on Telegram and then we could try to figure it out
13:31
It shows
Error code : 1292. Truncated incorrect date vale : '06-04-1991'
What shall I do
@@herdataproject thank you for responding..
Actually i was
Using small y instead of Y
I saw your coment then i understood..
You're welcome!
Hi there im slightly stuck with question 10, it comes up with error code unknown column 'termination' in field list, does anyone know why?
Hi Lakita, I think the issue is the name of the column.
Make sure it's the exact name of the column as is in the database
Thank you! May I ask, what did you save your data cleaning file as from MySql? Or did you save it from somewhere else?@@herdataproject
Thank you but where do i get this sample data from in order to do the same on my own ?
Hello check the description for the Telegram link and once you join, check the pinned messages
i get trouble with this query, the output is error :
SELECT GENDER, count(*) AS COUNT
FROM HR
WHERE AGE >= 18 AND TERMDATE = IS NOT NULL
group by GENDER;
because some part in termdate table is null? how can fix it?
Remove the = from TERMDATE = IS NULL. It should work
thank you so much for ur work.
You're most welcome!
I have seen half of this video while even I couldn't know you are Male or Female.
But video is osm fantastic ❤❤🎉🎉
can anyone explain the aim of this project mean why we make dishbord and i cant understand dishbord mean wht is the problem behind this that we make this typle dishbord
This is an informational dashboard. It helps management understand the status of their employees at any point in time
Hi there, I am getting an error on question 5. This is my code:
SELECT AVG(DATEDIFF(termdate, hire_date))/365 AS average_length_of_employment
FROM hr
WHERE termdate =18;
This is the error I am receiving: Error Code: 1525. Incorrect DATE value: '0000-00-00'.
Any assistance would be appreciated. Thanx
You should run the set sql_mode query first to sort this error
thank you so much 👌👌
Please I tried to download the dataset from the link on the description box, but link can't be reach. Can you help please?
Hi the link works. You can try it again here
t.me/+7ms4DzzAYVlhYmZk
If it doesn't work, try downloading from Github
github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI
🙏 Am grateful, thanks
I will explore the options
thank you for the video you created, it's an awesome project and the way you explain it makes the code syntax easier to understand. anyway, may i add this project to my resume?
Thank you, and thanks for watching.
Yes you can for now but I'd advise finding your own dataset and working on it so that you can make sure you have mastered the concepts as well as the entire process. And also in case a recruiter came across this video they don't assume you just did copy and paste.
@@herdataproject thank you again madam. I'll write your name on the description and perhaps the link to the video. After completing this project, i will start to build my own one.
I'm glad to hear that. Good luck and if you encounter any issues you can always reach out on the Telegram Community. Plenty of people willing to help
Thank you Ma@@herdataproject
@@herdataproject thank you again mam and good luck for your next project as well, im joining right now.
Hi, it was a great video.
I'm working on a HR Analytics Portfolio Project.
The way You imported data in Power BI, can you please tell me how make this Dashboard Interactive ?
Hi,you can add slicers to the report to be able to filter by your fields of interest. That will make the dashboard interactive
if everyone has trouble in the termdate column error when you update , let take the code " SET SQL_SAFE_UPDATES = 0" before the code update this colums. like this:
SET SQL_SAFE_UPDATES = 0;
UPDATE hr
SET termdate = IF(termdate IS NOT NULL AND termdate!='',date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00')
WHERE TRUE ;
Hello, how to solve the below problem in Microsoft sql
UPDATE HR
SET termdate = CONVERT(date, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' ';
Error - Conversion failed when converting date and/or time from character string.
you can modify the UPDATE statement to exclude the problematic rows that cannot be converted to a date.
UPDATE HR
SET termdate = CONVERT(date, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' ' AND ISDATE(termdate) = 1;
The additional condition ISDATE(termdate) = 1 is added to ensure that only valid date values are considered for conversion. The ISDATE function checks if the value can be converted to a valid date, and only rows where the 'termdate' value is a valid date will be updated.
Hope this helps
Please which software do you use for the screen recording?
OBS studio
I couldnt update termdate using PLSQL developer. STR_TO_DATE is not working. I tried using TO_CHAR(TERMDATE,'DD/MM/YYYY') , trunc(TERMDATE).. nothing works out. Datatype is Varchar 26. Plz help.
I fixed it.. following syntax works out..
UPDATE tablename
SET TERMDATE=cast(to_timestamp_tz(TERMDATE, 'yyyy-mm-dd hh24:mi:ss "UTC"')
as date)
WHERE TERMDATE IS NOT NULL;
Thank you so much for this. It will be very helpful for many other people using Oracle.
Irene, First I tried to download CSV, it became a text file, when I changed the file to CSV and imported to SQL, the data is not clear and has errors, please tell me step by step how to download the file from your GITHUB, and how to open in MySQL
You can also download it from the telegram if you're able to install the app. I think it should download as a CSV file. Once you join the group it'll be pinned at the top
While I'm writing same to same QUERY like you ❤❤😢😢
Hi, which exact command isn't working?
@@herdataproject Update command on birthday column, he reflects error like truncated incorrect date value 😭😭 I tired since 3 days not proceeding further 🙏
for the birthdate update, i keep getting truncated incorrect date value '6/29/1984', how do i correct that error
Change y to capital Y
@@ChinweAirdrop the birthdate column becomes null
Pls what's the solution?
@@ChinweAirdrop Thank you so much
when we are considering the term date 00000 to select the employees, what about the employees whose term date is in future?that means they are still in the company and we are not considering them
We can't verify that for sure so we assumed it was a data entry error and documented the same as limitation in our analysis.
@@herdataproject thank you so much for the ans.
Hi, for min and max ages did anyone not get negative values? My result doesn't show -42. It shows minimum value as 20. I tried copy pasting the query from github too. Same result. Would really appreciate any help:) Thank you for the video .
You are correct mine shows as 21 maybe because I am doing this some months after you had done yours
Why do I get different values with the exact same code?
Depends on the column you're referring to. For dates etc, you're running the code on a different date than I did. If that's not the case then please share a screenshot in the Telegram group to allow me to assist you further
Please i have a question...
When i query for "what is the gender breakdown of employee in the company" and SQL returns the result table.....is there a way to save the result so that when I connect powerBI to the SQL database I will have access to the result table from the query rather than exporting each result as a CSV because imagine you are working with a large company and the result table from your query is 3million rows I don't think I will be able to export that into excel easily? Do you get me?
Yes I get you. I think what you're looking for is something like a database view. Create a database view with the SQL query logic. Power BI can then directly connect to this view and retrieve data without running the query every time. I have never tried to use it before with PowerBI or even Tableau but I think it would work. You can try it out and let me know.
@@herdataproject I think you're right. Just did a quick Google search and I think database views might be the solution. Thanks for the reply
The dataset can be downloaded from Telegram or GitHub.
On Telegram check the pinned message
Telegram - t.me/+7ms4DzzAYVlhYmZk
GitHub - github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI
Please leave any questions or requests in the comments
Bhai data file nahi download ho raha hai
Using Microsoft sql and there is no str_to_date function, tried changing termdate data type but was not able to, tried convert, cast but it's not working, please help
In Microsoft SQL Server, you can use the CONVERT() function to convert a string to a date. The format string to use with CONVERT() is different from the one used with STR_TO_DATE() in MySQL.
UPDATE hr
SET termdate = CONVERT(DATETIME, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' '
In this example, the CONVERT() function is used to convert the termdate string to a datetime data type using the format code 120, which corresponds to the ISO 8601 format (yyyy-mm-dd hh:mi:ss(24h)).
Something to keep in mind is that the CONVERT() function can be affected by the default language setting of the SQL Server instance. If you're experiencing issues with the conversion, you can try using the SET LANGUAGE statement to set the language explicitly before running the UPDATE statement:
SET LANGUAGE us_english
UPDATE hr
SET termdate = CONVERT(DATETIME, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' '
I hope this helps : )
@@herdataproject thank you so much
You're welcome. I hope it worked
Still not working in Microsoft SQL server
when I try to change the termdate I get an error stating incorrect datetime value. mine is copied EXACTLY like yours so I'm so confused
Error Code: 1411. Incorrect datetime value: '2029-10-29 06:09:38 UTC' for function str_to_date
run this query first
SET SQL_MODE = ' ';
then run the update query
967 records showing age minus value due to date conversion, some dates in text value like 6/26/67 changed to 2067-6-26 instead of 1967.
I'm interested in, how to change these data to correct 1900 dates in the same command? anybody?
---
UPDATE hr
SET birthdate = CASE
WHEN birthdate LIKE '%/%' THEN date_format(str_to_date(birthdate, '%m/%d/%Y'), '%Y-%m-%d')
WHEN birthdate LIKE '%-%' THEN date_format(str_to_date(birthdate, '%m-%d-%Y'), '%Y-%m-%d')
ELSE NULL
END;
---
And great tutorial btw, really helpful, two thumbs up.
Thank you so much. More to come😊
So I don't think I would be accurate to convert those dates to 1967 and so on because we don't know for sure that is the value that is supposed to be there. But if you decide to do that then you would have to mention it in your final documentation.
I also noticed that, however, I believe Excel can be used in date conversion in this case.
Thank you for this project
Mine imported 17425 records instead of 22214
Any reason why this is so Ma'am?@@herdataproject
While operating on termdate
And executing the query the date format was changed but blank cells were not updated with ''0000-00-00"
They don't have to update to 0000-00-00. It all depends with the settings of your MySQL. If yours have remained as NULL it's okay. When writing the queries use
WHERE termdate IS NULL instead of WHERE termdate = '0000-00-00'
@@herdataproject sure and thank you
You're welcome
@@herdataproject sir will send that error in telegram group please help
@@herdataproject Is it a problem when working with Power BI, Because of a NULL value
Hey, i keep getting this error, can u tell me how will i be able to resolve this issues for following code:
UPDATE hr SET birthdate=CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate,'%m/%d/%y'),'%y-%m=%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate,'%m/%d/%y'),'%y-%m=%d') ELSE NULL END
Error Code: 1411. Incorrect datetime value: '06-04-1991' for function str_to_date 0.000 sec
Use Y instead of y.
Try to write the code exactly as it is in the video
@@herdataproject thank you but can you explain why the change of the Y. It would be very helpful
@@herdataproject it worked... thank you infinite...
Some functions like describe, str_to_date isnt working on my system
Which SQL dialect are you using?
MySQL
17:19:17 UPDATE hr SET birthdate=CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate,'%m/%d/%Y'),'%Y-%m-%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate,'%m-%d-%Y'),'%Y-%m-%d') ELSE NULL
END
Error Code: 1411. Incorrect datetime value: '2012-01-01' for function str_to_date 0.012 sec
Could you help me with this?
Hello use UPDATE IGNORE hr
Remove or comment ELSE from query and then try
hope this will work
I am having ERROR 1292 issue, can some1 help me?