Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

akasubi
8 - Asteroid

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

 

8 REPLIES 8
JosephSerpis
17 - Castor
17 - Castor

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

 

Ladarthure
14 - Magnetar
14 - Magnetar

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)

akasubi
8 - Asteroid

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?

JosephSerpis
17 - Castor
17 - Castor

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.

Ladarthure
14 - Magnetar
14 - Magnetar

@akasubi here is a screen shot of what you could do!

Capture.JPG

akasubi
8 - Asteroid

Thanks @Ladarthure 

This has done the job!

ThomasT
8 - Asteroid

@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
14 - Magnetar
14 - Magnetar

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’écran 2022-11-22 à 08.42.52.png

Labels