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

Quirolla
Quirolla Member
in Workbench

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 IDCategorical VariableCategorical DateReport Identifier
123Blue1/1/20183/1/2018
456Red2/1/20183/1/2018
789Green 3/1/20183/1/2018
123Blue1/1/20184/1/2018
456Red2/1/20184/1/2018
987Black 3/1/20184/1/2018
123Blue1/1/20185/1/2018
987Black 3/1/20185/1/2018
654Red4/1/20185/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. 

Best Answer

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Quirolla
    Quirolla Member

    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
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Quirolla
    Quirolla Member

    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! 

     

     

This discussion has been closed.

Categories