ETL Calculation
Please see above scenario. Those are quotes tied to a project. You can see that quote 90795(column D) is a first revision of quote 90439. My goal is to create a calculation that would show binary output as shown in column E. Basically check every 'Project Name' and if there is a revision to a quote (marked in column B), then count that revision and based on the same Revision ID from column B exclude that same Document Number from column D. Any ideas how to achieve that?
Thanks
Best Answer
-
Sure, lets say I have the following dataset, which matches the structure of the table you first provided:
The ETL will look something like:
CODE (you can copy and paste this into your ETL interface):
{"contentType":"domo/dataflow-actions","data":[{"name":"TEST - ETL","id":"3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc","type":"LoadFromVault","gui":{"x":192,"y":144,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"15ae0f19-79bd-4331-ab41-e9ff46d7bf29","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Revision Left != null","id":"800d6a16-4a67-41e0-9243-233f1485fd26","type":"Filter","gui":{"x":312,"y":252},"dependsOn":["3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Revision Left","operator":"NN","andFilterList":[]}]},{"name":"Set GOAL 0","id":"0696fc17-5739-4459-bef9-5b7fa2389c1e","type":"Constant","gui":{"x":432,"y":252},"dependsOn":["800d6a16-4a67-41e0-9243-233f1485fd26"],"removeByDefault":false,"notes":[],"fields":[{"name":"GOAL","type":"LONG","value":"0"}]},{"name":"Join Data","id":"b2563d4e-063a-4da8-a02c-1ef735742311","type":"MergeJoin","gui":{"x":576,"y":144},"dependsOn":["3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc","0696fc17-5739-4459-bef9-5b7fa2389c1e"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc","step2":"0696fc17-5739-4459-bef9-5b7fa2389c1e","keys1":["Project Name","Document Number"],"keys2":["Project Name","Revision Left"],"schemaModification1":[],"schemaModification2":[{"name":"Project Name","rename":"","remove":true},{"name":"Revision Left","rename":"Set GOAL 0.Revision Left","remove":true},{"name":"Revision Right","rename":"Set GOAL 0.Revision Right","remove":true},{"name":"Document Number","rename":"Set GOAL 0.Document Number","remove":true}],"partitioningInputId":""},{"name":"GOAL replace Null with 1","id":"8578bba4-4477-4299-9091-2337ef705667","type":"ExpressionEvaluator","gui":{"x":684,"y":144},"dependsOn":["b2563d4e-063a-4da8-a02c-1ef735742311"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"GOAL","expression":"COALESCE(GOAL,1)","settings":null}]}]}
From there you'll only need to set an Output dataset and be done. In the Join I dropped all the other columns I didn't care for, since the only goal was to have that GOAL flag. Hope this helps
0
Answers
-
For this you'll need to join your table in on itself (left join) using the as key for the left table the
Project Name
+Document Number
and for the right table theProject Name
+Revision Left
that way you'll only get entries to the new columns when the a match is found, you set this to 0 and then do a formula tile to replace nulls with 1's (for when there was no revisions to it), to avoid duplication since I'm not sure if the same one can have multiple revisions, I'd either do a distinct or a group by, in the case that you care to know how many revisions there are (in this case your logic would be reveresed, 0 would indicate no revisions while anything above 0 that there is one or more)0 -
Can you u explain what do you mean by joining the table on itself? I have never done it and not sure I follow your logic. I already removed 'multiple' revisions from a quote. Currently only have the latest revisions for a quote. Last step is to remove the initial quotes. So in my case I can only have 1 revision for a quote.
0 -
Sure, lets say I have the following dataset, which matches the structure of the table you first provided:
The ETL will look something like:
CODE (you can copy and paste this into your ETL interface):
{"contentType":"domo/dataflow-actions","data":[{"name":"TEST - ETL","id":"3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc","type":"LoadFromVault","gui":{"x":192,"y":144,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"15ae0f19-79bd-4331-ab41-e9ff46d7bf29","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Revision Left != null","id":"800d6a16-4a67-41e0-9243-233f1485fd26","type":"Filter","gui":{"x":312,"y":252},"dependsOn":["3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Revision Left","operator":"NN","andFilterList":[]}]},{"name":"Set GOAL 0","id":"0696fc17-5739-4459-bef9-5b7fa2389c1e","type":"Constant","gui":{"x":432,"y":252},"dependsOn":["800d6a16-4a67-41e0-9243-233f1485fd26"],"removeByDefault":false,"notes":[],"fields":[{"name":"GOAL","type":"LONG","value":"0"}]},{"name":"Join Data","id":"b2563d4e-063a-4da8-a02c-1ef735742311","type":"MergeJoin","gui":{"x":576,"y":144},"dependsOn":["3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc","0696fc17-5739-4459-bef9-5b7fa2389c1e"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"3bc7880b-ec51-4a2f-adc9-8418d2dc2dcc","step2":"0696fc17-5739-4459-bef9-5b7fa2389c1e","keys1":["Project Name","Document Number"],"keys2":["Project Name","Revision Left"],"schemaModification1":[],"schemaModification2":[{"name":"Project Name","rename":"","remove":true},{"name":"Revision Left","rename":"Set GOAL 0.Revision Left","remove":true},{"name":"Revision Right","rename":"Set GOAL 0.Revision Right","remove":true},{"name":"Document Number","rename":"Set GOAL 0.Document Number","remove":true}],"partitioningInputId":""},{"name":"GOAL replace Null with 1","id":"8578bba4-4477-4299-9091-2337ef705667","type":"ExpressionEvaluator","gui":{"x":684,"y":144},"dependsOn":["b2563d4e-063a-4da8-a02c-1ef735742311"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"GOAL","expression":"COALESCE(GOAL,1)","settings":null}]}]}
From there you'll only need to set an Output dataset and be done. In the Join I dropped all the other columns I didn't care for, since the only goal was to have that GOAL flag. Hope this helps
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive