community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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

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

 

Alteryx Certified Partner
Alteryx Certified Partner

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

 

Quasar
Quasar

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)

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?

Alteryx Certified Partner
Alteryx Certified Partner

Hi @akasubi as mentioned by @Ladarthur create a field you can then join back onto your data. So that you have maxdate essentially appended to all rows of your data.

Highlighted
Quasar
Quasar

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

Capture.JPG

Asteroid

Thanks @Ladarthur 

This has done the job!

Labels