AnsweredAssumed Answered

Import Raw data and clean up records

Question asked by AlexE on Apr 2, 2015
Latest reply on Apr 5, 2015 by DavidJondreau

Title

Import Raw data and clean up records

Post

Hi, 

I'm importing a bunch of raw sales data from hundreds of different store locations. I have basically 2 tables that are needed for this operation. a StoreInfo table that contains all covered locations (about 400 locations) with full store info. And a second table for Raw Data (RAW)

We get raw sales data in an excel spreadsheet from a vendor. I can import the data and convert it for our needs just fine. My issue is that I have about 70,000 records in my raw data table but most of it is from locations that I don't care about (basically the ones NOT in my StoreInfo) and it increases the file size quite a bit. 

So what I'm trying to figure out is an efficient method for either skipping the non-covered stores during import or to then simply delete the non-covered stores from the raw sales data. 

I created a loop that compares StoreID's from the RawData to StoreInfo using a Find each time and deletes the RawData record if that record contains data from a non-covered location but it takes over 20 minutes on a Quad-Core MacBook Pro with plenty of RAM. I'm convinced I can make this faster, just can't seem to figure it out. 

Go to Layout (RAW)

Go to Record/Request/Page (First)
Loop
  set Variable ($compare ; RAW:StoreID)
  Go to Layout (StoreInfo)
  Enter Find Mode
  Set Field (StoreInfo:StoreID ; $compare)
  Perform Find
  if (Get (FoundCount) = 0)
    Go To Layout (RAW)
    Delete Record/Request
  Else
    Go To Layout (RAW)
  End If
  Go To Record/Request/Page (Next; Exit after last)
End Loop

 

Any "faster" ideas or script steps I might have missed?? 

Outcomes