Recursive Issue Help - New Magic ETL 2.0

RichYute

Hello Community.

I am trying to follow the recursive guide here, but I am obviously missing a step or two as my data is not showing the historical at all. In fact whenever I add new data, the new data is the only thing that seems to be available.

Can someone kindly help and put me on the right path please?

Sample_Data.xlsx

As you can see in the video link below my filter is showing nothing in preview.

https://www.screencast.com/t/AICnxijUMmGh

I know the video doesn't show the append part, but all this was done and I am just about to bang my head against the wall now.

MarkSnodgrass

ok. you have things opposite of what I do. Below in the code block is my dataflow. Take what is in this code block and copy and paste it into a new empty dataflow. It might not like the input output datasets in your instance, but it should allow you to at least see the tiles that are important and click around. (Magic ETLs are JSON files, so if you select all of them and choose copy to clipboard, you can paste them into a code editor and see the JSON, or into another ETL)

{"contentType":"domo/dataflow-actions","data":[{"name":"Earthquakes - USGS","id":"32c77085-0e06-4955-a7e0-eef3f4a147fd","type":"LoadFromVault","gui":{"x":156,"y":96,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"260a9612-c7e1-40ba-b18f-63a0a040df88","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"recentVersionCutoffMs":0,"columnSettings":{}},{"name":"Earthquakes Recursive Complete v2 1","id":"3c58798d-e014-43fb-b078-fd04c56fae8e","type":"LoadFromVault","gui":{"x":156,"y":270,"color":3701798,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"cf45ca6f-6413-42ce-87c9-dadb0668823c","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"recentVersionCutoffMs":0,"columnSettings":{}},{"name":"Select ID Column","id":"175d6006-afed-432d-8809-5ce538f9e7d4","type":"SelectValues","gui":{"x":312,"y":186,"color":null,"colorSource":null},"dependsOn":["3c58798d-e014-43fb-b078-fd04c56fae8e"],"removeByDefault":false,"notes":[],"fields":[{"name":"id","rename":null}]},{"name":"Join Data","id":"7d2bebc9-7d74-454e-b292-a461c22c8a3e","type":"MergeJoin","gui":{"x":444,"y":96,"color":null,"colorSource":null},"dependsOn":["32c77085-0e06-4955-a7e0-eef3f4a147fd","175d6006-afed-432d-8809-5ce538f9e7d4"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"32c77085-0e06-4955-a7e0-eef3f4a147fd","step2":"175d6006-afed-432d-8809-5ce538f9e7d4","keys1":["id"],"keys2":["id"],"schemaModification1":[],"schemaModification2":[{"name":"id","rename":"Select ID Column.id","remove":false}]},{"name":"Filter to new rows","id":"15d8aad8-692f-4523-88bf-de06ecd48c95","type":"Filter","gui":{"x":600,"y":96,"color":null,"colorSource":null},"dependsOn":["7d2bebc9-7d74-454e-b292-a461c22c8a3e"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Select ID Column.id","rightExpr":null,"operator":"NL","andFilterList":[]}]},{"name":"Append Rows","id":"58c97bef-c9b5-408e-9d5e-85512b464654","type":"UnionAll","gui":{"x":792,"y":216,"color":null,"colorSource":null},"dependsOn":["15d8aad8-692f-4523-88bf-de06ecd48c95","3c58798d-e014-43fb-b078-fd04c56fae8e"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_MATCHES","strict":false},{"name":"Earthquakes Recursive Complete v2","id":"c3296cda-73f4-422f-a7fa-08de72b20ca3","type":"PublishToVault","gui":{"x":1116,"y":120,"color":null,"colorSource":null},"dependsOn":["58c97bef-c9b5-408e-9d5e-85512b464654"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"cf45ca6f-6413-42ce-87c9-dadb0668823c","type":"DataFlow","name":"Earthquakes Recursive Complete v2"},"versionChainType":"REPLACE"}]}

You want to filter on the recursive dataset, not the one that is the new data, so you want to look for where your recursive dataset is null for that date. This way, it only adds new dates.

MarkSnodgrass

What criteria are you using for your filter tile? Based on what you are doing it should be where the date is null from your right table. Here's a screenshot of a basic recursive dataflow that I did, and yours appears to be set up similarly.

RichYute

Hi Mark ,

First thanks for your quick reply and also for showing me yet another example...For filter let me show you what I have instead - see link - https://www.screencast.com/t/LYIdrV1V

MarkSnodgrass

ok. you have things opposite of what I do. Below in the code block is my dataflow. Take what is in this code block and copy and paste it into a new empty dataflow. It might not like the input output datasets in your instance, but it should allow you to at least see the tiles that are important and click around. (Magic ETLs are JSON files, so if you select all of them and choose copy to clipboard, you can paste them into a code editor and see the JSON, or into another ETL)

{"contentType":"domo/dataflow-actions","data":[{"name":"Earthquakes - USGS","id":"32c77085-0e06-4955-a7e0-eef3f4a147fd","type":"LoadFromVault","gui":{"x":156,"y":96,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"260a9612-c7e1-40ba-b18f-63a0a040df88","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"recentVersionCutoffMs":0,"columnSettings":{}},{"name":"Earthquakes Recursive Complete v2 1","id":"3c58798d-e014-43fb-b078-fd04c56fae8e","type":"LoadFromVault","gui":{"x":156,"y":270,"color":3701798,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"cf45ca6f-6413-42ce-87c9-dadb0668823c","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"recentVersionCutoffMs":0,"columnSettings":{}},{"name":"Select ID Column","id":"175d6006-afed-432d-8809-5ce538f9e7d4","type":"SelectValues","gui":{"x":312,"y":186,"color":null,"colorSource":null},"dependsOn":["3c58798d-e014-43fb-b078-fd04c56fae8e"],"removeByDefault":false,"notes":[],"fields":[{"name":"id","rename":null}]},{"name":"Join Data","id":"7d2bebc9-7d74-454e-b292-a461c22c8a3e","type":"MergeJoin","gui":{"x":444,"y":96,"color":null,"colorSource":null},"dependsOn":["32c77085-0e06-4955-a7e0-eef3f4a147fd","175d6006-afed-432d-8809-5ce538f9e7d4"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"32c77085-0e06-4955-a7e0-eef3f4a147fd","step2":"175d6006-afed-432d-8809-5ce538f9e7d4","keys1":["id"],"keys2":["id"],"schemaModification1":[],"schemaModification2":[{"name":"id","rename":"Select ID Column.id","remove":false}]},{"name":"Filter to new rows","id":"15d8aad8-692f-4523-88bf-de06ecd48c95","type":"Filter","gui":{"x":600,"y":96,"color":null,"colorSource":null},"dependsOn":["7d2bebc9-7d74-454e-b292-a461c22c8a3e"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Select ID Column.id","rightExpr":null,"operator":"NL","andFilterList":[]}]},{"name":"Append Rows","id":"58c97bef-c9b5-408e-9d5e-85512b464654","type":"UnionAll","gui":{"x":792,"y":216,"color":null,"colorSource":null},"dependsOn":["15d8aad8-692f-4523-88bf-de06ecd48c95","3c58798d-e014-43fb-b078-fd04c56fae8e"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_MATCHES","strict":false},{"name":"Earthquakes Recursive Complete v2","id":"c3296cda-73f4-422f-a7fa-08de72b20ca3","type":"PublishToVault","gui":{"x":1116,"y":120,"color":null,"colorSource":null},"dependsOn":["58c97bef-c9b5-408e-9d5e-85512b464654"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"cf45ca6f-6413-42ce-87c9-dadb0668823c","type":"DataFlow","name":"Earthquakes Recursive Complete v2"},"versionChainType":"REPLACE"}]}

You want to filter on the recursive dataset, not the one that is the new data, so you want to look for where your recursive dataset is null for that date. This way, it only adds new dates.

RichYute

Hi Mark,

Your method worked, but the under-lying issue for me was that I was a filtering (Date) which seems to have change format during transformation and as result messed everything up for me. Domo is so powerful and unforgiven, but thanks again for your time and effort.🍻