ETL Calculation

Options
zuchu
zuchu Member
in Magic ETL
image.png

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

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓
    Options

    Sure, lets say I have the following dataset, which matches the structure of the table you first provided:

    image.png

    The ETL will look something like:

    image.png

    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

Answers

  • marcel_luthi
    marcel_luthi Coach
    Options

    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 the Project 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)

  • zuchu
    zuchu Member
    Options

    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.

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓
    Options

    Sure, lets say I have the following dataset, which matches the structure of the table you first provided:

    image.png

    The ETL will look something like:

    image.png

    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

Categories