Logic - link original submit date to all subsequent back orders

Jbrorby

Hello. I'm wondering if anyone has ever had a similar scenario/concept and have come up with good ways to achieve the desired outcome.

I've attached a screen shot of real data. The goal is to get the submitted date of 7/5/2024 (A6)

linked to orders in rows 7 and 8.

The story of this data is that order id 16403748 was created. From this order, a new back order id of 16406011 was created. Then, from 16406011, a new back order id of 16416888 was created.

The only thing I've been able to think of is to perform multiple joins as shown in the attached image. However, the issue is the number of back orders and new ids that can occur is unlimited, (it could be 0, 2, 100… although realistically probably no more than 5-10 at most) and each new back order id only has the most former order id linked to it, and not the very original. For example, 8C has the order id of 7B rather then the order id of 6B which would make things easier.

There is no data value that all 3 of these rows will share and so I cannot group by or partition by anything that I can think of.

snap for domo community question.PNG

Manasi_Panov

Hello @Jbrorby ,

I had a similar problem to solve in the past. I was able to do it with a Python script in ETL:

Here is the result table:

Copy and past this in your ETL to find the solution and the Python script:

{"contentType":"domo/dataflow-actions","data":[{"icon":"python","name":"Python Script","category":"scripting","description":"Write a custom Python script to manipulate your data","helpUrl":"/articles/360045485833#1.","type":"PythonEngineAction","feature":"dataflows-scripting-python","config":{"curView":"configuration","hasSources":true,"hasTargets":true,"buttons":[{"id":"CODE_TEMPLATE","text":"Use code template","icon":"term"},{"id":"RUN_PREVIEW","text":"Run Preview","icon":"play-circle-outline"},{"id":"CANCEL_PREVIEW","text":"Cancel Preview","icon":"x-circle-outline"}]},"dirtyPreview":false,"previewRunning":false,"validationRunning":false,"removeByDefault":true,"configured":true,"ready":true,"enabled":true,"selectedTabHasMessage":false,"dependsOn":["ed1a6969-2fb0-49e9-a286-afc0dce95311"],"children":["8560b80c-e7e1-48b3-b20b-ecbd544bdd01"],"outputSchema":[{"name":"submitted at","id":"submitted at","type":"DATETIME","typeLabel":"Timestamp","icon":"icon-calendar-simple","metadata":{}},{"name":"order id","id":"order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"original order id","id":"original order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"first order","id":"first order","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}}],"previewData":{},"gui":{"x":204,"y":48,"color":null,"colorSource":null,"sampleJson":null},"notes":[],"fillMissingWithNull":true,"additions":[{"name":"submitted at","dataType":"DATETIME"},{"name":"order id","dataType":"LONG"},{"name":"original order id","dataType":"LONG"},{"name":"first order","dataType":"LONG"}],"script":"# Import the domomagic package into the script\nfrom domomagic import *\nimport pandas as pd\nimport numpy as np\n\n# read data from inputs into a data frame\ninput1 = read_dataframe('Domo Forum Answer')\n\n# write your script here\n\nrelations = {}\n\nfor index, row in input1.iterrows():\n order = row['order id']\n replacement = row['original order id']\n if not pd.isna(replacement):\n relations[order] = replacement\n\n# Function to find the first order in a gives series of orders\ndef find_first_order(order):\n first_order = order\n while order in relations:\n first_order = order\n order = relations[order]\n return first_order\n\n \n# Create a new column 'first order' in the DataFrame\ninput1['first order'] = input1['order id'].apply(find_first_order)\n\n# write a data frame so it's available to the next action\nwrite_dataframe(input1)","condaEnv":null,"id":"07446cf8-e2c6-4a26-ba22-a532e3d4493d","settings":{},"label":"Python Script"},{"icon":"join-left-outer","name":"Join Data","category":"combine-data","description":"Combine rows from two DataSets into one.","type":"MergeJoin","helpUrl":"/articles/360044876194#3.","config":{"curView":"configuration","maxTargets":2,"minTargets":2,"hasSources":true,"hasTargets":true},"dirtyPreview":false,"previewRunning":false,"validationRunning":false,"removeByDefault":false,"configured":true,"ready":true,"enabled":true,"selectedTabHasMessage":false,"dependsOn":["07446cf8-e2c6-4a26-ba22-a532e3d4493d","ed1a6969-2fb0-49e9-a286-afc0dce95311"],"children":["c7f25cef-2c99-4d7c-8009-d4c4e34fe816"],"outputSchema":[{"name":"submitted at","id":"submitted at","type":"DATETIME","typeLabel":"Timestamp","icon":"icon-calendar-simple","metadata":{}},{"name":"order id","id":"order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"original order id","id":"original order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"first order","id":"first order","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"NEW_COLUMN","id":"NEW_COLUMN","type":"DATE","typeLabel":"Date","icon":"icon-calendar-simple","metadata":{}}],"previewData":{},"gui":{"x":312,"y":132,"color":null,"colorSource":null,"sampleJson":null},"notes":[],"step1":"07446cf8-e2c6-4a26-ba22-a532e3d4493d","step2":"ed1a6969-2fb0-49e9-a286-afc0dce95311","joinType":"LEFT OUTER","keys1":["first order"],"keys2":["order id"],"on":null,"schemaModification1":[],"schemaModification2":[{"name":"submitted at","rename":"NEW_COLUMN","remove":false},{"name":"order id","rename":"Domo Forum Answer.order id","remove":true},{"name":"original order id","rename":"Domo Forum Answer.original order id","remove":true}],"relationshipType":"MANY_TO_MANY","id":"8560b80c-e7e1-48b3-b20b-ecbd544bdd01","settings":{},"label":"Join Data"}]}

Manasi_Panov

Hello @Jbrorby ,

I had a similar problem to solve in the past. I was able to do it with a Python script in ETL:

Here is the result table:

Copy and past this in your ETL to find the solution and the Python script:

{"contentType":"domo/dataflow-actions","data":[{"icon":"python","name":"Python Script","category":"scripting","description":"Write a custom Python script to manipulate your data","helpUrl":"/articles/360045485833#1.","type":"PythonEngineAction","feature":"dataflows-scripting-python","config":{"curView":"configuration","hasSources":true,"hasTargets":true,"buttons":[{"id":"CODE_TEMPLATE","text":"Use code template","icon":"term"},{"id":"RUN_PREVIEW","text":"Run Preview","icon":"play-circle-outline"},{"id":"CANCEL_PREVIEW","text":"Cancel Preview","icon":"x-circle-outline"}]},"dirtyPreview":false,"previewRunning":false,"validationRunning":false,"removeByDefault":true,"configured":true,"ready":true,"enabled":true,"selectedTabHasMessage":false,"dependsOn":["ed1a6969-2fb0-49e9-a286-afc0dce95311"],"children":["8560b80c-e7e1-48b3-b20b-ecbd544bdd01"],"outputSchema":[{"name":"submitted at","id":"submitted at","type":"DATETIME","typeLabel":"Timestamp","icon":"icon-calendar-simple","metadata":{}},{"name":"order id","id":"order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"original order id","id":"original order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"first order","id":"first order","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}}],"previewData":{},"gui":{"x":204,"y":48,"color":null,"colorSource":null,"sampleJson":null},"notes":[],"fillMissingWithNull":true,"additions":[{"name":"submitted at","dataType":"DATETIME"},{"name":"order id","dataType":"LONG"},{"name":"original order id","dataType":"LONG"},{"name":"first order","dataType":"LONG"}],"script":"# Import the domomagic package into the script\nfrom domomagic import *\nimport pandas as pd\nimport numpy as np\n\n# read data from inputs into a data frame\ninput1 = read_dataframe('Domo Forum Answer')\n\n# write your script here\n\nrelations = {}\n\nfor index, row in input1.iterrows():\n order = row['order id']\n replacement = row['original order id']\n if not pd.isna(replacement):\n relations[order] = replacement\n\n# Function to find the first order in a gives series of orders\ndef find_first_order(order):\n first_order = order\n while order in relations:\n first_order = order\n order = relations[order]\n return first_order\n\n \n# Create a new column 'first order' in the DataFrame\ninput1['first order'] = input1['order id'].apply(find_first_order)\n\n# write a data frame so it's available to the next action\nwrite_dataframe(input1)","condaEnv":null,"id":"07446cf8-e2c6-4a26-ba22-a532e3d4493d","settings":{},"label":"Python Script"},{"icon":"join-left-outer","name":"Join Data","category":"combine-data","description":"Combine rows from two DataSets into one.","type":"MergeJoin","helpUrl":"/articles/360044876194#3.","config":{"curView":"configuration","maxTargets":2,"minTargets":2,"hasSources":true,"hasTargets":true},"dirtyPreview":false,"previewRunning":false,"validationRunning":false,"removeByDefault":false,"configured":true,"ready":true,"enabled":true,"selectedTabHasMessage":false,"dependsOn":["07446cf8-e2c6-4a26-ba22-a532e3d4493d","ed1a6969-2fb0-49e9-a286-afc0dce95311"],"children":["c7f25cef-2c99-4d7c-8009-d4c4e34fe816"],"outputSchema":[{"name":"submitted at","id":"submitted at","type":"DATETIME","typeLabel":"Timestamp","icon":"icon-calendar-simple","metadata":{}},{"name":"order id","id":"order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"original order id","id":"original order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"first order","id":"first order","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"NEW_COLUMN","id":"NEW_COLUMN","type":"DATE","typeLabel":"Date","icon":"icon-calendar-simple","metadata":{}}],"previewData":{},"gui":{"x":312,"y":132,"color":null,"colorSource":null,"sampleJson":null},"notes":[],"step1":"07446cf8-e2c6-4a26-ba22-a532e3d4493d","step2":"ed1a6969-2fb0-49e9-a286-afc0dce95311","joinType":"LEFT OUTER","keys1":["first order"],"keys2":["order id"],"on":null,"schemaModification1":[],"schemaModification2":[{"name":"submitted at","rename":"NEW_COLUMN","remove":false},{"name":"order id","rename":"Domo Forum Answer.order id","remove":true},{"name":"original order id","rename":"Domo Forum Answer.original order id","remove":true}],"relationshipType":"MANY_TO_MANY","id":"8560b80c-e7e1-48b3-b20b-ecbd544bdd01","settings":{},"label":"Join Data"}]}