日本語
Hi,
I am working on order date column. few dates are missing and I want to add missing dates rows by filling other features with 0 value.
I will appreciate in this regards
Hi @so ,
First you'll want to use "shift" to get the rows of missing dates you will need to create.
1) Window > Shift
2) Shift Up on the Dates column by 1 row
3) Create a computed column to calculate the rows you will need to create (notice the minus 1, because if it's only 1 day apart we don't need to create any row):
4) Using another compute column convert the number into an array (note that you need to create this "if statement" to cater for the maximum number of values you have, as there is no looping in compute column statements):
5) This next compute column could be incorporated in the previous if statement, so i am actually going to EDIT the above, but wanted to leave it there for reference... SO I am going to change the above to this (slightly more complex logic):
6) This next step is trivial but important. Of course the last row is not going to have a valid value, as it has no valid "next row" to look at, so we need to remove the error value from the cell. Double click on the cell with the red warning dot, to replace it with nothing.
7) I need to split this column into separate columns now:
7)depivot this column to create the rows I want
😎 Remove the blank rows:
9) (optional) change the date to date format and remove the columns you don't need, resulting in:
On to your next question, there are two things you need to do:
1) Create a constant:
2a) Window > Aggregate
2b) Use the Constant column to Group the records, and then the offset you use will be 42 (i used 3 because my dataset was smaller).
Hope that helps!
Callum
