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

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.

Date Time

Common Use Cases

Accepted answers

jdunkerley79

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

demoLastUpdate.yxmd

All comments

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.

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:
MostRecentRecs.tiff

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335