How do i fill in rows of data with previous row info?
Best Answer
-
Is there a way you can add a row number to your original dataset? We can in theory do this in ETL but without having a real column to sort on, I'm not sure if the method I've used that worked for a small dataset will keep on working all the time.
Original:
Backfilled (you could rename or remove columns as needed, but I left most there so that you'd know where this comes from, the way it has been structure will also allow it to be backfill should the year in Row 4 would be missing):
This is what the ETL Looks like:
And here's the code you could copy to the clipboard and paste to have it recreated on your end:
{"contentType":"domo/dataflow-actions","data":[{"name":"TEST - Backfill","id":"cf74d6f1-44ee-4af8-af86-3f4fa7bfc647","type":"LoadFromVault","gui":{"x":24,"y":348,"color":3238043,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"fe80921c-8e3b-4f53-b605-54393dd9f1f3","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Constants","id":"4281ab41-abc7-42cb-a153-978b311f5748","type":"Constant","gui":{"x":120,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["cf74d6f1-44ee-4af8-af86-3f4fa7bfc647"],"removeByDefault":false,"notes":[],"fields":[{"name":"Helper","type":"LONG","expr":null,"value":"1"}]},{"name":"Rank & Window","id":"6c20902a-36b8-446f-a9ac-31d8b9361345","type":"WindowAction","gui":{"x":216,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["4281ab41-abc7-42cb-a153-978b311f5748"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"RowNumber","operation":{"type":"RANKING","operationType":"ROW_NUMBER"}}],"orderRules":[{"column":"Helper","caseSensitive":false,"ascending":true}],"groupRules":[]},{"name":"SelectYears","id":"f50f0e8e-6c1b-4201-b67d-a4ad504586fe","type":"SelectValues","gui":{"x":384,"y":432,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345"],"removeByDefault":false,"notes":[],"fields":[{"name":"Year","rename":null},{"name":"RowNumber","rename":null},{"name":"Helper","rename":null}]},{"name":"SelectMonths","id":"5a8bbefa-d8ad-406f-a963-d1a550106c5b","type":"SelectValues","gui":{"x":456,"y":252,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345"],"removeByDefault":false,"notes":[],"fields":[{"name":"Month","rename":null},{"name":"RowNumber","rename":null},{"name":"Helper","rename":null}]},{"name":"YearValues","id":"0a1ba943-267d-475b-bae9-a4a476f91c1b","type":"Filter","gui":{"x":480,"y":432,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f50f0e8e-6c1b-4201-b67d-a4ad504586fe"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Year","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"MonthValues","id":"1661e24d-d73d-46c9-99e8-fd753dd2b1ba","type":"Filter","gui":{"x":564,"y":252,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5a8bbefa-d8ad-406f-a963-d1a550106c5b"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Month","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Join Possible Years","id":"22d7db5d-c722-46ba-806b-c1901337de88","type":"MergeJoin","gui":{"x":588,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345","0a1ba943-267d-475b-bae9-a4a476f91c1b"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"6c20902a-36b8-446f-a9ac-31d8b9361345","step2":"0a1ba943-267d-475b-bae9-a4a476f91c1b","keys1":["Helper"],"keys2":["Helper"],"schemaModification1":[],"schemaModification2":[{"name":"Year","rename":"YearValues.Year","remove":false},{"name":"Helper","rename":"","remove":true},{"name":"RowNumber","rename":"YearValues.RowNumber","remove":false}],"partitioningInputId":""},{"name":"Join Possible Months","id":"8c5877d9-ccfd-49fc-ae1c-1b23bcb96723","type":"MergeJoin","gui":{"x":708,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["22d7db5d-c722-46ba-806b-c1901337de88","1661e24d-d73d-46c9-99e8-fd753dd2b1ba"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"22d7db5d-c722-46ba-806b-c1901337de88","step2":"1661e24d-d73d-46c9-99e8-fd753dd2b1ba","keys1":["Helper"],"keys2":["Helper"],"schemaModification1":[],"schemaModification2":[{"name":"Month","rename":"MonthValues.Month","remove":false},{"name":"Helper","rename":"","remove":true},{"name":"RowNumber","rename":"MonthValues.RowNumber","remove":false}],"partitioningInputId":""},{"name":"Get Latest Year and Moth Rows","id":"2fc9caa4-31db-4d32-bb26-cc008b702f1e","type":"GroupBy","gui":{"x":816,"y":444,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["8c5877d9-ccfd-49fc-ae1c-1b23bcb96723"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"RowNumber"}],"partitionedAggregation":false,"fields":[{"name":"LatestYearRow","expression":"MAX(CASE WHEN RowNumber >= YearValues.RowNumberTHEN YearValues.RowNumberEND)","settings":null},{"name":"LatestMonthRow","expression":"MAX(CASE WHEN RowNumber>= MonthValues.RowNumberTHEN MonthValues.RowNumberEND)","settings":null}]},{"name":"Limit to only the latest combination","id":"5e9d2ebb-27a1-46a5-8ed5-85ab15ca9bec","type":"MergeJoin","gui":{"x":936,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["2fc9caa4-31db-4d32-bb26-cc008b702f1e","8c5877d9-ccfd-49fc-ae1c-1b23bcb96723"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"8c5877d9-ccfd-49fc-ae1c-1b23bcb96723","step2":"2fc9caa4-31db-4d32-bb26-cc008b702f1e","keys1":["RowNumber","YearValues.RowNumber","MonthValues.RowNumber"],"keys2":["RowNumber","LatestYearRow","LatestMonthRow"],"schemaModification1":[{"name":"YearValues.RowNumber","rename":"","remove":true},{"name":"MonthValues.RowNumber","rename":"","remove":true}],"schemaModification2":[{"name":"RowNumber","rename":"","remove":true}],"partitioningInputId":""},{"name":"TEST - Backfilled","id":"cf727414-7253-4f02-a708-1e61cc5d7a6f","type":"PublishToVault","gui":{"x":1062,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5e9d2ebb-27a1-46a5-8ed5-85ab15ca9bec"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"00141f72-88f2-41dd-a0d7-800e1096d8d8","type":"DataFlow","name":"TEST - Backfilled","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}Let us know if this helps!
1
Answers
-
Is there a way you can add a row number to your original dataset? We can in theory do this in ETL but without having a real column to sort on, I'm not sure if the method I've used that worked for a small dataset will keep on working all the time.
Original:
Backfilled (you could rename or remove columns as needed, but I left most there so that you'd know where this comes from, the way it has been structure will also allow it to be backfill should the year in Row 4 would be missing):
This is what the ETL Looks like:
And here's the code you could copy to the clipboard and paste to have it recreated on your end:
{"contentType":"domo/dataflow-actions","data":[{"name":"TEST - Backfill","id":"cf74d6f1-44ee-4af8-af86-3f4fa7bfc647","type":"LoadFromVault","gui":{"x":24,"y":348,"color":3238043,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"fe80921c-8e3b-4f53-b605-54393dd9f1f3","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Constants","id":"4281ab41-abc7-42cb-a153-978b311f5748","type":"Constant","gui":{"x":120,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["cf74d6f1-44ee-4af8-af86-3f4fa7bfc647"],"removeByDefault":false,"notes":[],"fields":[{"name":"Helper","type":"LONG","expr":null,"value":"1"}]},{"name":"Rank & Window","id":"6c20902a-36b8-446f-a9ac-31d8b9361345","type":"WindowAction","gui":{"x":216,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["4281ab41-abc7-42cb-a153-978b311f5748"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"RowNumber","operation":{"type":"RANKING","operationType":"ROW_NUMBER"}}],"orderRules":[{"column":"Helper","caseSensitive":false,"ascending":true}],"groupRules":[]},{"name":"SelectYears","id":"f50f0e8e-6c1b-4201-b67d-a4ad504586fe","type":"SelectValues","gui":{"x":384,"y":432,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345"],"removeByDefault":false,"notes":[],"fields":[{"name":"Year","rename":null},{"name":"RowNumber","rename":null},{"name":"Helper","rename":null}]},{"name":"SelectMonths","id":"5a8bbefa-d8ad-406f-a963-d1a550106c5b","type":"SelectValues","gui":{"x":456,"y":252,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345"],"removeByDefault":false,"notes":[],"fields":[{"name":"Month","rename":null},{"name":"RowNumber","rename":null},{"name":"Helper","rename":null}]},{"name":"YearValues","id":"0a1ba943-267d-475b-bae9-a4a476f91c1b","type":"Filter","gui":{"x":480,"y":432,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f50f0e8e-6c1b-4201-b67d-a4ad504586fe"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Year","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"MonthValues","id":"1661e24d-d73d-46c9-99e8-fd753dd2b1ba","type":"Filter","gui":{"x":564,"y":252,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5a8bbefa-d8ad-406f-a963-d1a550106c5b"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Month","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Join Possible Years","id":"22d7db5d-c722-46ba-806b-c1901337de88","type":"MergeJoin","gui":{"x":588,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345","0a1ba943-267d-475b-bae9-a4a476f91c1b"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"6c20902a-36b8-446f-a9ac-31d8b9361345","step2":"0a1ba943-267d-475b-bae9-a4a476f91c1b","keys1":["Helper"],"keys2":["Helper"],"schemaModification1":[],"schemaModification2":[{"name":"Year","rename":"YearValues.Year","remove":false},{"name":"Helper","rename":"","remove":true},{"name":"RowNumber","rename":"YearValues.RowNumber","remove":false}],"partitioningInputId":""},{"name":"Join Possible Months","id":"8c5877d9-ccfd-49fc-ae1c-1b23bcb96723","type":"MergeJoin","gui":{"x":708,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["22d7db5d-c722-46ba-806b-c1901337de88","1661e24d-d73d-46c9-99e8-fd753dd2b1ba"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"22d7db5d-c722-46ba-806b-c1901337de88","step2":"1661e24d-d73d-46c9-99e8-fd753dd2b1ba","keys1":["Helper"],"keys2":["Helper"],"schemaModification1":[],"schemaModification2":[{"name":"Month","rename":"MonthValues.Month","remove":false},{"name":"Helper","rename":"","remove":true},{"name":"RowNumber","rename":"MonthValues.RowNumber","remove":false}],"partitioningInputId":""},{"name":"Get Latest Year and Moth Rows","id":"2fc9caa4-31db-4d32-bb26-cc008b702f1e","type":"GroupBy","gui":{"x":816,"y":444,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["8c5877d9-ccfd-49fc-ae1c-1b23bcb96723"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"RowNumber"}],"partitionedAggregation":false,"fields":[{"name":"LatestYearRow","expression":"MAX(CASE WHEN RowNumber >= YearValues.RowNumberTHEN YearValues.RowNumberEND)","settings":null},{"name":"LatestMonthRow","expression":"MAX(CASE WHEN RowNumber>= MonthValues.RowNumberTHEN MonthValues.RowNumberEND)","settings":null}]},{"name":"Limit to only the latest combination","id":"5e9d2ebb-27a1-46a5-8ed5-85ab15ca9bec","type":"MergeJoin","gui":{"x":936,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["2fc9caa4-31db-4d32-bb26-cc008b702f1e","8c5877d9-ccfd-49fc-ae1c-1b23bcb96723"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"8c5877d9-ccfd-49fc-ae1c-1b23bcb96723","step2":"2fc9caa4-31db-4d32-bb26-cc008b702f1e","keys1":["RowNumber","YearValues.RowNumber","MonthValues.RowNumber"],"keys2":["RowNumber","LatestYearRow","LatestMonthRow"],"schemaModification1":[{"name":"YearValues.RowNumber","rename":"","remove":true},{"name":"MonthValues.RowNumber","rename":"","remove":true}],"schemaModification2":[{"name":"RowNumber","rename":"","remove":true}],"partitioningInputId":""},{"name":"TEST - Backfilled","id":"cf727414-7253-4f02-a708-1e61cc5d7a6f","type":"PublishToVault","gui":{"x":1062,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5e9d2ebb-27a1-46a5-8ed5-85ab15ca9bec"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"00141f72-88f2-41dd-a0d7-800e1096d8d8","type":"DataFlow","name":"TEST - Backfilled","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}Let us know if this helps!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 745 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive