Skip to main content
Question

Analysing changes in table with imports from different times

J
  • Joxxe
  • New Participant
  • 1 reply

Hi,

I am importing raw data periodically, let’s say monthly.

Every import includes a data field with the date of import.

Given the repetitive imports, the key differentiator (let's say Supplier ID) is repeating.

I added a locked view where I filter by import date.

=> I now want to analyse the data for two of the imports, e.g., 1. January and 1. June and see if any supplier was added or removed and what data in a specific field (e.g. performance) has changed.

 

The closest I got was partly manual and didn't work: I copied and pasted all Supplier IDs from both import dates into Excel and then removed duplicates.

In a new table with auto number index, I added that list into two linked Supplier ID fields, and each of the fields linked to the views 1. Jan und 1. June. Then, I added two look-up fields for the performance of each date.

My goal was to have a list showing either the relevant performance per different date fields or blanks if newly created or deleted suppliers.

Unfortunately, both performance fields always show the same input despite the link to different views with different date filters and different performance values.

I am stuck and would highly appreciate a point in the right direction or even a better solution.

Cheers

Jochen

2 replies

mtrebinonixon

Hi Joxxe, how are you?

When importing your data this would be going to a “Metrics” table where every record is the monthly metrics of every active supplier.

So to be able to have your Metrics neatly grouped by Supplier, you would need to create a “Suppliers” table, and link every Metrics record to its corresponding Supplier record by using a “Linked Record” column type (“Link to Suppliers”).

This link can be done automatically if you set the “Supplier ID” as the Primary ID column of the “Suppliers” table and change your “Supplier ID” column in your “Metrics” table to a “Link to Suppliers” column. This would allow you to save on an automation and the link will be done immediatelly. It will also take care of creating new Suppliers, if new ones appear in the current month’s import.

Then, in the Supplier table you can make calculations based on the Supplier’s linked Metrics:

  • Lookup the first Metric of the Supplier to determine when they were created.
  • Lookup Metrics of specific months and use a formula field to compare them.
  • Count Metrics for a specific month to know how many and which Customers were deleted.

Let me know if understood correctly!


And if you need some guidance to get it up & running we can do a quick meet, feel free to schedule one here: https://calendar.app.google/BDT6pyk35Xk1F9jB8


Best regards,

Matt Nixon

DisraeliGears01
1 person likes this
  • DisraeliGears01
    DisraeliGears01
DisraeliGears01
Forum|alt.badge.img+6

It’s definitely best to reconstruct your base as Matt mentions for longer term, but in the short term you can accomplish these kind of comparisons manually by using a combination of filters, sorting, and (potentially) groups.

For instance, start with a filter for upload dates on 1/1 and 6/1. Then sort by Supplier ID, which should set matched Supplier IDs next to one another. Then if you can also group by Supplier ID, which breaks out the groups so they pair up and you can run column calculations. This is a very manual and temporary solution (when you have hundreds of suppliers, this kind of grid is annoying to scroll through and keep track of) but it can work in a pinch. 

Reply


Most helpful members this week

Terms & Conditions