Is there a way to add unique id or constant to workbench

Quirolla · October 2018

I am seeking to aggrigate a historic set of data. The problem is the data pull date, a critical peice of identification, is stored in the title of the report - and does not exist in any of the fields. There is no way to identify WHEN in time the data in a row exsisted, or if it differs from the next time that identical row appears in another report.

Example

Row ID	Categorical Variable	Categorical Date	Report Identifier
123	Blue	1/1/2018	3/1/2018
456	Red	2/1/2018	3/1/2018
789	Green	3/1/2018	3/1/2018
123	Blue	1/1/2018	4/1/2018
456	Red	2/1/2018	4/1/2018
987	Black	3/1/2018	4/1/2018
123	Blue	1/1/2018	5/1/2018
987	Black	3/1/2018	5/1/2018
654	Red	4/1/2018	5/1/2018

I have tried to add a concatenated statement of the max date on a report but it returns the max date of each row... I would prefer not to load each report manually and blend/etl it since it's a standard report that should be workbenched. Is there a way to pull in data from an excel sheet's meta info or title? or a way to attach a unique constant per run? A date would be ideal but i will settle for anything i can use to compare unique id to unique id transactionally.

AS · October 2018

Yes, historically existing rows will need a different solution. Perhaps you could start with the dates you wanted in the spreadsheet, then you could remove that date column and then insert the transform in the workbench job, and see if they append to each other.

AS · October 2018

In your workbench job, you can add a "Transform", a new column of a calculated field. today function.PNG

This will insert a new column called 'Test Date', and if you set the workbench job to append, each new set of rows will have a new value in that 'Test Date' column.

Quirolla · October 2018

Thanks, that will absolutely help for data imports in the future - but appending historic data it will all look like "today" when in actuality it is different variables (past dates) that apply to each dataset exclusively.

AS · October 2018

Yes, historically existing rows will need a different solution. Perhaps you could start with the dates you wanted in the spreadsheet, then you could remove that date column and then insert the transform in the workbench job, and see if they append to each other.

Quirolla · October 2018

This might be the only (work-around) solution with the current way Workbench runs.

I actually did end up doing just this and it works.

Thank you!

Is there a way to add unique id or constant to workbench

Best Answer

Answers

Categories