Forum Discussion
Missing Dates with Part Number and Value
Hi All,
First post here, ill try to explain my issue as precis as possible.
My data set looks like this:
Table Name: "Table 1"
PartNo: Date: Value:
12345 2018-01-01 1
12345 2018-01-02 5
12345 2018-01-05 3
54321 2018-01-01 10
54321 2018-01-10 20
What i want:
PartNo: Date: Value:
12345 2018-01-01 1
12345 2018-01-02 5
12345 2018-01-03 0
12345 2018-01-04 0
12345 2018-01-05 3
54321 2018-01-01 10
54321 2018-01-02 0
54321 2018-01-03 0
...
54321 2018-01-08 0
54321 2018-01-09 0
54321 2018-01-10 20
I have about 30 000 parts and missing dates when the Value is 0. I would like to add all dates for all parts but with Value 0.
If anyone has any idea to solve this it would help me alot!
Many thanks in advance!
/MitchConnor
4 Replies
- Mariusz
Community Champion
Hi Anonymous
Adding Calendar table would be a good start, it would sort the problem of missing dates.Hope this helps.
MariuszHi Mariusz ,
Thank you for your reply!
Yes i got this. In the visualisation this helps.
The issue is that i also need to calculate the development from day to day in a new column, in the table.
I got this already, but this is wrong since i am missing dates where the value should be 0 and also i get aggregation issues in the visualisation.
I have tried to Join the 2 tables: Dates and my dataset. But i need all dates for each part with the value 0 if they dont exist in the dataset. And that i cannot solve :)
- Mariusz
Hi Anonymous ,
You can merge the tables in query Editor (Marge Queries), just make sure Date table is First and Join Kind is left outer, after just use Replace Values "blanks" with 0.Hope this helps
Mariusz
