Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Loading exception on first record

Hello All,

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

Example:

   Key                              Date Creation

12185204/01/2016 10:24
12185604/01/2016 10:49
12185605/01/2016 03:32
12185605/01/2016 05:43
12185605/01/2016 07:44
12185605/01/2016 10:31
12187304/01/2016 12:22
12187504/01/2016 13:28
12188305/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

1 Solution

Accepted Solutions

Re: Loading exception on first record

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;

6 Replies

Re: Loading exception on first record

Hi,

check firstsortedvalue()

Regards

Regards,
Prashant Sangle
sureshqv
Esteemed Contributor III

Re: Loading exception on first record

try

=FirstSortedValue(key, -[ Date Creation])

Re: Loading exception on first record

You can also try,

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

balrajahlawat
Esteemed Contributor

Re: Loading exception on first record

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

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;

Not applicable

Re: Loading exception on first record

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

Community Browser