Display max date of whole dataset as new column using In-DB

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

Date Time

In Database

In-Database

Accepted answers

Ladarthure

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)

All comments

JosephSerpis

Hi @akasubi you could use the Summarize In-DB Tool and use the max function to create a column.

Ladarthure

Hi,

akasubi

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?

akasubi

Thanks @Ladarthure

This has done the job!

ThomasT

@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

Ladarthure

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.

Capture d’écran 2022-11-22 à 08.42.52.png

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703