topic Re: Loading exception on first record in QlikView

Loading exception on first record

Fri, 24 Jun 2016 11:08:35 GMT

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

Re: Loading exception on first record

PrashantSangle — Fri, 24 Jun 2016 11:11:02 GMT

Hi,

check firstsortedvalue()

Regards

Re: Loading exception on first record

Chanty4u — Fri, 24 Jun 2016 11:14:11 GMT

try

=FirstSortedValue(key, -[ Date Creation])

Re: Loading exception on first record

tamilarasu — Fri, 24 Jun 2016 11:17:52 GMT

You can also try,

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

Re: Loading exception on first record

Anonymous — Fri, 24 Jun 2016 11:20:26 GMT

try with FirstValue() at script, like this?

load

FirstValue(Key) as Key,

FirstValue(DateTimeCreation) as TimeCreation

Resident Your_TableName;

Re: Loading exception on first record

sunny_talwar — Fri, 24 Jun 2016 11:26:13 GMT

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;

Re: Loading exception on first record

Fri, 24 Jun 2016 11:47:37 GMT

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