Description
Add missing rows from an integer or date sequence.
Examples
Add missing IDs in the current range, with values copied from above:
Add missing dates in a specified range as 0 sales:
Create an integer sequence from a dataset with no rows:
Inputs
One.
Options
•Set Rows Added to Missing integers or Missing dates depending on the type of sequence.
•Set From column to the column with the incomplete sequence.
•Set Minimum to:
oFrom data to set the minimum value in the sequence to the minimum value in the selected column.
oUser defined to set the minimum value to a value set by the user. Must be in yyyy-M-d or yyyy-MM-dd format for dates, regardless of the date format in the dataset.
•Set Maximum to:
oFrom data to set the maximum value in the sequence to the maximum value in the selected column.
oUser defined to set the maximum values to a value set by the user. Must be in yyyy-M-d or yyyy-MM-dd format for dates, regardless of the date format in the dataset.
•Set Order according to whether you want the dataset sorted Ascending or Descending by the selected column.
•Set New values to:
oBlank to set new row values to empty.
oCopy from above to set new row values to the same as the next row sorted above from the input dataset.
oCopy from below to set new row values to the same as the next row sorted below from the input dataset.
oUser defined to allow the user to choose a value for new row values. Set With value for the new value to use.
Notes
•Existing sequence values are unchanged.
•You can add leading zeros to integers values created using the Pad transform.
•Dates added will try to follow the format of existing dates, using the date formats listed in the Dates tab of the Preferences window. If you want ISO dates, make sure yyyy-MM-dd is above yyyy-M-d.
•You can change the format of dates created using the DateTime format transform.
•If you only want to add some values from a sequence (e.g. odd numbers or weekdays) then you can do this by using Sequence followed by Filter to remove the unwanted values. You might need to use Calculate with the Modulus and/or Day of week operation to create a column to filter on.
•Use the Cross transform if you want to combine multiple sequences. E.g. to cross hourly times (00:00, 01:00 ... 23:00 ) with a date sequence.
See also
•Video: How to insert missing dates
•Fill