I'm connected to a table in SQL server that has dates as one of the columns. I would like to create a new column that displays the max date of the whole dataset. Therefore, the data for this column will be the same for all rows.
In SQL Server you'd do this by writing a nested query as a column in the select statement.
e.g. select id, (select max(date) from table) from table
How can I do this in Alteryx using the in-db tools. Due to the way my workflow works, I can't just write is in the connect in-db tool.
Thanks
afk
Solved! Go to Solution.
Hi @akasubi you could use the Summarize In-DB Tool and use the max function to create a column.
Hi,
what I would do is to have one summarize with the max date, then create a "fake join field" with value "a" and join it with the main datastream in whihc you also create a field with "a" value (both with formula tools)
Hi @JosephSerpis
If i use the summarize tool and do max(date), I'm only left with one column. How would I add this to the original dataset?
Hi @akasubi as mentioned by @Ladarthure create a field you can then join back onto your data. So that you have maxdate essentially appended to all rows of your data.
@akasubi here is a screen shot of what you could do!
Thanks @Ladarthure
This has done the job!
@Ladarthure long time since this post. Maybe you can help me on a similar issue.
I have a database table with a range of dates [date_1], out of those dates I want to find the max_date (easy with summarize) --> [date_1_max]. Now next steps is to only download rows from another database table with another date range ie [date_2] where the dates are 'newer'.
With your solution displayed, is the easiest way IN-DB to append the max date as a new column (fake field) and then use an IN-DB filter to check if [date_1_max] > [date_2]?
Thanks,
Thomas
Hi @ThomasT,
it depends on your databases, but there could be multiple schemas around this.
Here are 2 "blueprint" to do it.
The first one is using the full in db route where you only use a join key you make for this case (here 1), and then filter
The second one is using dynamic input in db.