LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Importing / Filtering data based on max values in data source

    Employee
    Posted 08-20-2015 16:59

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: sicmate

    Hi guys,

    Hoping someone may be able to point me in the right direction with this. I'm pulling the last 20 days worth of data from a one database and appending it to an excel file. I want to do is look at what the max date is in the excel file, and then filter the database output to only bring MaxDate + 1 (ImportStartDate) to yesterday (ImportEndDate) - essentially only bringing in data that doesn't already exist in the excel file.

    I've managed to use the agg-ex node to generate ImportStartDate and ImportEndDate in correctly (in 1 row), however I'm having issues filtering the data from the database based on this criteria.

    I've tried using the x-ref and filter by group nodes separately to achieve this but have had no luck. I've also tried messing around with making the import criteria a Parameter and using that in a filter node but I couldn't get it to work (not even sure if parameters can be used as variables?). I'm guessing there might be an easy way to do this that I'm missing so any help would be appreciated.

    Thanks in advance.


  • 2.  RE: Importing / Filtering data based on max values in data source

    Employee
    Posted 08-20-2015 17:40

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    You're on the right track. After getting the Start/End dates, use a Join (X-Ref would work, too) to output only when the database date is between the two boundaries. See attached.
    Attachments:
    extractDateRange.brg


  • 3.  RE: Importing / Filtering data based on max values in data source

    Employee
    Posted 08-20-2015 18:07

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: sicmate

    Thanks so much ryeh, that worked perfectly. I was close but just couldn't get the syntax right in the join.

    Could you please tell me what is the significance of putting '1' in both the left & rightinputkey?


  • 4.  RE: Importing / Filtering data based on max values in data source

    Employee
    Posted 08-20-2015 18:26

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Using 1 (or TRUE) as both keys is essentially telling the join to match every row from the left input to every row from the right input. It's often called a Cartesian join.


  • 5.  RE: Importing / Filtering data based on max values in data source

    Employee
    Posted 08-20-2015 18:29

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: sicmate

    I see, that is really handy. I was using specific fields which was also causing an error. Thanks again for your help ryeh, much appreciated.