Forum Discussion
Need help to create rows in table
I have a input table as below
| Product | category | Sales | Date |
| P1 | C1 | 100 | 31-Jan-23 |
| P1 | C2 | 200 | 30-Apr-23 |
| P2 | C1 | 100 | 30-Jun-22 |
I want an output table as given below. i.e for whichever months OF THE YEAR there are no sales it should insert row with 0 sales for EACH product & category combination .
| Product | category | Sales | Date |
| P1 | C1 | 100 | 31-Jan-23 |
| P1 | C1 | 0 | 28-Feb-23 |
| P1 | C1 | 0 | 31-Mar-23 |
| P1 | C1 | 0 | 30-Apr-23 |
| P1 | C1 | 0 | 31-May-23 |
| P1 | C1 | 0 | 30-Jun-23 |
| P1 | C1 | 0 | 31-Jul-23 |
| P1 | C1 | 0 | 31-Aug-23 |
| P1 | C1 | 0 | 30-Sep-23 |
| P1 | C1 | 0 | 31-Oct-23 |
| P1 | C1 | 0 | 30-Nov-23 |
| P1 | C1 | 0 | 31-Dec-23 |
| P1 | C2 | 0 | 31-Jan-23 |
| P1 | C2 | 0 | 28-Feb-23 |
| P1 | C2 | 0 | 31-Mar-23 |
| P1 | C2 | 200 | 30-Apr-23 |
| P1 | C2 | 0 | 31-May-23 |
| P1 | C2 | 0 | 30-Jun-23 |
| P1 | C2 | 0 | 31-Jul-23 |
| P1 | C2 | 0 | 31-Aug-23 |
| P1 | C2 | 0 | 30-Sep-23 |
| P1 | C2 | 0 | 31-Oct-23 |
| P1 | C2 | 0 | 30-Nov-23 |
| P1 | C2 | 0 | 31-Dec-23 |
| P2 | C1 | 0 | 31-Jan-22 |
| P2 | C1 | 0 | 28-Feb-22 |
| P2 | C1 | 0 | 31-Mar-22 |
| P2 | C1 | 0 | 30-Apr-22 |
| P2 | C1 | 0 | 31-May-22 |
| P2 | C1 | 100 | 30-Jun-22 |
| P2 | C1 | 0 | 31-Jul-22 |
| P2 | C1 | 0 | 31-Aug-22 |
| P2 | C1 | 0 | 30-Sep-22 |
| P2 | C1 | 0 | 31-Oct-22 |
| P2 | C1 | 0 | 30-Nov-22 |
| P2 | C1 | 0 | 31-Dec-22 |
Appreciate if you can help with this
1 Reply
Hey ashwinkolte ,
it's not clear, why the resulting table does not start on June 2022 for all products and does not end on December 2023 for all products/categories.
Next to that, it's not a good idea to create a fact table containing rows with a "zero" measurement. Instead I recommend creating a data model adhering to the star stema paradigm. Using a dedicated calendar table allows you to activate the option "Show items with no data" on the data visualization:
Regards,
Tom
