Forum Discussion
Reduce data rows
I've a table of purchased items stored in the Data Warehouse. The table has almost 20 million records. The table maintains daily transactions and hence the number if rows is huge. I want to find a w...
Hey Anonymous !
You can write your SQL like below:
SELECTEXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,EXTRACT(MONTH FROM YOUR_DATE_FIELD) AS month,
CUSTOMER, GROUP, MATERIAL,SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITYFROM YOUR_TABLEGROUP BYEXTRACT(YEAR FROM YOUR_DATE_FIELD),EXTRACT(MONTH FROM YOUR_DATE_FIELD),
CUSTOMER,MATERIAL,GROUPIf this helps, please mark this question as solved to help people find quickly in our community!
Hey Anonymous !
Can you write a SQL to extract the data? If yes, you can try use a group BY, or even create a view to reduce.
If you can write a SQL, you can use the Group BY function on Power Query (Group By in Power BI using Power Query Editor - SqlSkull).
If this helps, please mark this question as solved to help people find quickly in our community!
Regards,
Marcel
Thanks. Yes, I can write SQL statement as I import the data from Oracle DB.
I'm not expert with SQL, so I was wondering how would grouping discard the data and just keep the month and year?
Hey Anonymous !
You can write your SQL like below:
SELECTEXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,EXTRACT(MONTH FROM YOUR_DATE_FIELD) AS month,
CUSTOMER, GROUP, MATERIAL,SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITYFROM YOUR_TABLEGROUP BYEXTRACT(YEAR FROM YOUR_DATE_FIELD),EXTRACT(MONTH FROM YOUR_DATE_FIELD),
CUSTOMER,MATERIAL,GROUPIf this helps, please mark this question as solved to help people find quickly in our community!