How can a add a column with MIN(Date) OVER another field in load script?

[ildikohalasz84]

I have:

LOAD

    [KEY],

    [SubmittedDate]

FROM Table1;

Then I want another column where I only have the min submitted date of the key itself as there are multiple submissions.

Min([SubmittedDate] OVER [KEY]) AS [MinSubmittedDateOfKey].

Desired output would be:

[Key]   [SubmittedDate]   [MinSubmittedDateOfKey]

x           30.08.2018.                10.08.2018.

x           18.08.2018.                10.08.2018.

x           10.08.2018.                10.08.2018.

h           18.07.2018.                18.06.2018.

h           18.06.2018.                18.06.2018.

h           20.08.2018.                18.06.2018. 

Thanks.

[JordyWegman · Accepted solution]

Hi,

Try this:

Table:
LOAD
    [KEY],
    [SubmittedDate]
FROM Table1;

Left Join(Table)
LOAD
    [KEY],
    Min([SubmittedDate]) as MinSubmittedDateOfKey
FROM Table1
Group by [KEY];

Jordy

Climber

[pepecc]

Try this.

LOAD

    [KEY],

    [SubmittedDate],

  MinDate

FROM Table1

JOIN

LOAD

    [KEY],

   min( [SubmittedDate]) Mindate

FROM Table1

GROUP BY Key;

Best regards.

[ildikohalasz84]

It works, thanks.