Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ildikohalasz84
New Contributor

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

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.

1 Solution

Accepted Solutions
Partner
Partner

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

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

Work smarter, not harder
3 Replies
Partner
Partner

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

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

Work smarter, not harder
pepecc
New Contributor III

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

Try this.

 

LOAD

    [KEY],

    [SubmittedDate],

  MinDate

FROM Table1

JOIN

LOAD

    [KEY],

   min( [SubmittedDate]) Mindate

FROM Table1

GROUP BY Key;

 

Best regards.

 

 

 

 

ildikohalasz84
New Contributor

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

It works, thanks.