Forgive me I am new! - Needing Help With a Datetime

[JohnMaty]

HI Everyone,

I am BRAND NEW to Alteryx and I have what I think is a simple issue.  I am trying to pull information from a table based upon a last update date for each record.  I only want records that were updated since the last time the workflow ran.  Basically, I have this scenario:

1. Get the "LastRunDate"  (I do not know how to store or pull this).  Obviously I will need a default start date.

1b. Update the "LastRunDate" (I do not know how to do this)

2. Go to Table#1

3. Select * from Table#1 where Last_Update_Date >= "LastRunDate"

4.Insert rows into TargetTable#1

5.Repeat

Any help would be greatly appreciated.

[s_pichaipillai]

@JohnMaty

i think you can achieve this by below steps

1.Create a control table say CREATE TABLE Control (ProcessName varchar(50) not null, ProcessLastRunDate Datetime not null)

2.In your output tool use POST SQL option , Write update statement to update  ProcessLastRunDate field in conrtol table

3.now in your Input table Select * from Table#1 where Last_Update_Date >= (select ProcessLastRunDate from   Control where ProcessName ='Process1')

4. load this data into Target table

HOPE this helps

thanks

Saravanan

[jdunkerley79]

If the target table is on the same server could you do:

Select * from Table#1 where Last_Update_Date >= (SELECT MAX(Last_Update_Date) FROM TargetTable#1)

Alternatively the suggestion from @s_pichaipillai would work.

[JohnMaty]

Sadly this is on a read only server.  I can store the date in a heartbeat table on a linked server.

[jdunkerley79 · Accepted solution]

Think you are going to need to use a dynamic input and replace part of the query.

Attached a very raw demo of what I mean

[mbarone]

I often have the same need as you.  Here is what I do in; maybe it will help.

The very first time I do this, I pull the data from whatever table in whatever database, and in a formula tool I add a "DateTimeAppend" field that has a formula of "DateTimeNow".  This will stick a column in your data with what I call the "load time".  Very last tool is an Output tool that throws the data in a YXDB.

Then, I take my original data flow, and next to it stick that YXDB I just created.  I union the two flows together, and then use a Summarize tool to group all the fields, with the exception of the DateTimeAppend field, which I take the Max of.  Then put a Block Until Done tool after the Summarize, and output to the same YXDB.

This will give you a YXDB with the "most recent" record for everyone.  

Here is a snapshot: