Loading exception on first record

[#1]

Hello All,

I need to load a table but only the first record based on the date and time creation:

Example:

   Key                              Date Creation

121852	04/01/2016 10:24
121856	04/01/2016 10:49
121856	05/01/2016 03:32
121856	05/01/2016 05:43
121856	05/01/2016 07:44
121856	05/01/2016 10:31
121873	04/01/2016 12:22
121875	04/01/2016 13:28
121883	05/01/2016 04:27

As you can notice for example the key 121856 shows different times in table but I want to load just the first 1 created (the line in red in the above table).

Any idea?

Many Thanks,

Hasvine

[PrashantSangle]

Hi,

check firstsortedvalue()

Regards

[Chanty4u]

try

=FirstSortedValue(key, -[ Date Creation])

[tamilarasu]

You can also try,

Date(Min(Date(Date#([Date Creation],'DD/MM/YYYY hh:mm'))),'DD/MM/YYYY hh:mm')

[Anonymous]

try with FirstValue() at script, like this?

load

FirstValue(Key) as Key,

FirstValue(DateTimeCreation) as TimeCreation

Resident Your_TableName;

[sunny_talwar · Accepted solution]

There might be numerous ways to do this, one of them would be to do a right join of the min Date Creation by Key to get only those rows where Date Creation is the minimum.

Table:

LOAD @1 as Key,

    @2 as [Date Creation]

FROM

[https://community.qlik.com/thread/222366]

(html, codepage is 1252, no labels, table is @1);

Right Join (Table)

LOAD Key,

  Min([Date Creation]) as [Date Creation]

Resident Table

Group By Key;

[#7]

Hi Sunny,

It works. I was having some difficulty to understand the concept but your explanation helped me a lot to understand how it works.

Thanks again and thanks to everyone for your help. 🙂

Kind Regards,

Hasvine