2 Obstacles to Achieving Automated Reporting - asking for help

Options

3PL send me daily storage data. In the file each sheet reflects that day's data, for example, sheet '4.26' reflect 26th April data, sheet '4.25' reflects 25th April data. those sheets are accumulated in one file called 'month to date' storage report with each day's data in different sheet.

I'd like to connect this daily data file with a DOMO designed card to automatically append data to the card.

I have two difficulties:

  1. how to distinguish data from different date? In DOMO, by appending data, it will accumulate each day's data without DATE distinguished. Now I think I can achieve it by VBA. Can I achieve it by some DOMO functions?
  2. how to append data with the smallest manual workload?

thank you.

Best Answers

  • david_cunningham
    Answer ✓
    Options

    I would recommend setting up an ETL with a file upload dataset as an input. You can then in this ETL use the "Add Formula" tile to grab the current date.

    Once you execute the ETL once, you can go back in and use the output as an input and append to create a recursive data flow. For example.

    Then you'll be able to load the current days data into the excel file connector dataset, which will then kickoff your ETL that will add a column with the current timestamp and append to your master dataset.

    Example output

    A few things to note. You'll want to make sure your columns are consistent so the append will work as expected. There are other options, such as the email connector, or SFTP depending on how exactly you receive your data.

    Let me know if you have any questions!

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    One way you could do it would be to append to a dataset using an ETL.

    Create a dataset and make sure it has a column to store the date. In the ETL dataflow, set a formula value with the current date and that in the date column within the dataset. Use the "append" tile to append data each day, the column value will act as a timestamp.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • david_cunningham
    Answer ✓
    Options

    I would recommend setting up an ETL with a file upload dataset as an input. You can then in this ETL use the "Add Formula" tile to grab the current date.

    Once you execute the ETL once, you can go back in and use the output as an input and append to create a recursive data flow. For example.

    Then you'll be able to load the current days data into the excel file connector dataset, which will then kickoff your ETL that will add a column with the current timestamp and append to your master dataset.

    Example output

    A few things to note. You'll want to make sure your columns are consistent so the append will work as expected. There are other options, such as the email connector, or SFTP depending on how exactly you receive your data.

    Let me know if you have any questions!

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    One way you could do it would be to append to a dataset using an ETL.

    Create a dataset and make sure it has a column to store the date. In the ETL dataflow, set a formula value with the current date and that in the date column within the dataset. Use the "append" tile to append data each day, the column value will act as a timestamp.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **