Creating a constant value for each month

lvtrujillot

Hi all!

I want to build a Pivot Table Card with columns being each month from start to current date , rows as IDs and each value to be a constant number. However, in the dataset there's one row per ID showing start date, its status and the constant number (ID-1 ; 01/01/2016; active; 150). So, when building the card the constant value appears only in the month for start_date as expected, but I'd like to see this value for each month where its status were active.

I'm thinking in creating a dataflow with an extra date column, that duplicates each ID's information from its start date til today if its status is active, but I'd love to hear if there's an easier way to accomplish this!

| ID | Jan-21 | Feb-21 | ... | Mar-22 |

1 | 150 | 150 |.... | 150 |

Thanks!

MarkSnodgrass

I put together some test data and was able to do what you want to do. My test data had an ID column, a date column and a value column, which is the constant that will be repeated after the first entry. Here's a screenshot of my ETL:

Also, you can copy and past this line into a blank ETL and it will have all the information in the tiles for you:

{"contentType":"domo/dataflow-actions","data":[{"name":"Test Constant Month 1","id":"fd5ef49d-5317-4abc-ab43-02a363f6bbb1","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"49941246-fc36-48c8-a5a6-077b9cff470c","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Constants","id":"050c89db-5621-4c79-ae4a-94093298cee1","type":"Constant","gui":{"x":144,"y":180,"color":null,"colorSource":null},"dependsOn":["fd5ef49d-5317-4abc-ab43-02a363f6bbb1"],"removeByDefault":false,"notes":[],"fields":[{"name":"JoinKey","type":"LONG","expr":null,"value":"1"}]},{"name":"Group By All Months","id":"974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","type":"GroupBy","gui":{"x":252,"y":84,"color":null,"colorSource":null},"dependsOn":["050c89db-5621-4c79-ae4a-94093298cee1"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Date"},{"name":"JoinKey"}],"partitionedAggregation":false,"fields":[{"name":"Count","source":"ID","type":"COUNT_ALL","valuefield":null}]},{"name":"Join Data","id":"42f6d24e-2a1d-4028-8866-5cf098274e07","type":"MergeJoin","gui":{"x":336,"y":168,"color":null,"colorSource":null},"dependsOn":["974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","050c89db-5621-4c79-ae4a-94093298cee1"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","step2":"050c89db-5621-4c79-ae4a-94093298cee1","keys1":["JoinKey"],"keys2":["JoinKey"],"schemaModification1":[],"schemaModification2":[{"name":"Date","rename":"StartDate","remove":false},{"name":"JoinKey","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"6e74aab4-f11c-42f8-98f1-3ebf29ffe706","type":"ExpressionEvaluator","gui":{"x":456,"y":168,"color":null,"colorSource":null},"dependsOn":["42f6d24e-2a1d-4028-8866-5cf098274e07"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"CASE when `Date` < `StartDate` then NULL ELSE `Value` END","fieldName":"Value","settings":null}]},{"name":"Test Constant Month","id":"b72a9ddb-2ff7-4fa4-a68a-dca58b5b997b","type":"PublishToVault","gui":{"x":588,"y":168,"color":null,"colorSource":null},"dependsOn":["6e74aab4-f11c-42f8-98f1-3ebf29ffe706"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"ff298a29-fe48-4f57-95ca-92e1d211b3e6","type":"DataFlow","name":"Test Constant Month","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}

Here is a breakdown of what each tile is doing

Add Constants - add a column called JoinKey with a constant value of 1 that will be used for the joining

Group By All Months - Creating a list of all months in the dataset by adding the date field and the JoinKey column in the select list and then aggregate by any field (this field doesn't matter, we just need it to get our distinct list of months)

Join Data - Perform a left join with the group by on the left and join on JoinKey. On the right side, alter the date column to be renamed to StartDate as we will use it in the next tile. You can drop the JoinKey on the right side.

Add Formula - Overwrite your value field from your dataset by looking for when the date field is less than the StartDate field and make it null, otherwise leave it as is.

Connect to your output dataset

When constructed as a pivot table as you described in the beginning, it should look like this:

Hope this helps.

MarkSnodgrass

I put together some test data and was able to do what you want to do. My test data had an ID column, a date column and a value column, which is the constant that will be repeated after the first entry. Here's a screenshot of my ETL:

Also, you can copy and past this line into a blank ETL and it will have all the information in the tiles for you:

{"contentType":"domo/dataflow-actions","data":[{"name":"Test Constant Month 1","id":"fd5ef49d-5317-4abc-ab43-02a363f6bbb1","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"49941246-fc36-48c8-a5a6-077b9cff470c","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Constants","id":"050c89db-5621-4c79-ae4a-94093298cee1","type":"Constant","gui":{"x":144,"y":180,"color":null,"colorSource":null},"dependsOn":["fd5ef49d-5317-4abc-ab43-02a363f6bbb1"],"removeByDefault":false,"notes":[],"fields":[{"name":"JoinKey","type":"LONG","expr":null,"value":"1"}]},{"name":"Group By All Months","id":"974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","type":"GroupBy","gui":{"x":252,"y":84,"color":null,"colorSource":null},"dependsOn":["050c89db-5621-4c79-ae4a-94093298cee1"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Date"},{"name":"JoinKey"}],"partitionedAggregation":false,"fields":[{"name":"Count","source":"ID","type":"COUNT_ALL","valuefield":null}]},{"name":"Join Data","id":"42f6d24e-2a1d-4028-8866-5cf098274e07","type":"MergeJoin","gui":{"x":336,"y":168,"color":null,"colorSource":null},"dependsOn":["974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","050c89db-5621-4c79-ae4a-94093298cee1"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"974f2a27-c169-4e07-b7f1-ebdc2f1fcc3f","step2":"050c89db-5621-4c79-ae4a-94093298cee1","keys1":["JoinKey"],"keys2":["JoinKey"],"schemaModification1":[],"schemaModification2":[{"name":"Date","rename":"StartDate","remove":false},{"name":"JoinKey","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"6e74aab4-f11c-42f8-98f1-3ebf29ffe706","type":"ExpressionEvaluator","gui":{"x":456,"y":168,"color":null,"colorSource":null},"dependsOn":["42f6d24e-2a1d-4028-8866-5cf098274e07"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"CASE when `Date` < `StartDate` then NULL ELSE `Value` END","fieldName":"Value","settings":null}]},{"name":"Test Constant Month","id":"b72a9ddb-2ff7-4fa4-a68a-dca58b5b997b","type":"PublishToVault","gui":{"x":588,"y":168,"color":null,"colorSource":null},"dependsOn":["6e74aab4-f11c-42f8-98f1-3ebf29ffe706"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"ff298a29-fe48-4f57-95ca-92e1d211b3e6","type":"DataFlow","name":"Test Constant Month","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}

Here is a breakdown of what each tile is doing

Add Constants - add a column called JoinKey with a constant value of 1 that will be used for the joining

Group By All Months - Creating a list of all months in the dataset by adding the date field and the JoinKey column in the select list and then aggregate by any field (this field doesn't matter, we just need it to get our distinct list of months)

Join Data - Perform a left join with the group by on the left and join on JoinKey. On the right side, alter the date column to be renamed to StartDate as we will use it in the next tile. You can drop the JoinKey on the right side.

Add Formula - Overwrite your value field from your dataset by looking for when the date field is less than the StartDate field and make it null, otherwise leave it as is.

Connect to your output dataset

When constructed as a pivot table as you described in the beginning, it should look like this:

Hope this helps.

b_rad

@MarkSnodgrass I am curious how to get the code to replicate an ETL. I could really use that.

Thanks,

Bharath.

MarkSnodgrass

@b_rad if you select everything in the code block above that contains {content-type .... and then press ctrl+c to copy and then go to your data center and create a new Magic ETL and then press ctrl+v it will create all the tiles for you. It can't create the input and output datasets, so those will appear greyed out, but you will have all the tiles in between with all of the work done in them.

b_rad

@MarkSnodgrass I got that part. I am more curious on how you got that code block for an ETL in the first place. I always end up in situations where I have to replicate an existing ETL and make a version of that ETL with modifications for another project. I use the copy ETL feature but I dont like the fact that it creates the output dataset and I have to manually cleanup everything. Using the code block seems more ideal.

MarkSnodgrass

@b_rad Ahh... sorry about that. In Magic ETL, click and drag your mouse over all the tiles you want to highlight. Once they are all selected some options show up on the left side. Choose copy to clipboard. Then, go to your other Magic ETL and press ctrl+v to paste those into another ETL.

b_rad

Wow!! That's an awesome feature. Thanks a lot for sharing it.

This is going to make my life so much easier. :)

lvtrujillot

Thank you @MarkSnodgrass !