topic Re: Earliest Value in QlikView

Earliest Value

Thu, 18 Feb 2016 08:21:23 GMT

T1:

ID	Name	Date	Text
100	xxx	10/1/2015	test1
100	xxx	10/1/2015	test2
200	yyy	10/2/2015	test5
200	yyy	10/2/2015	test6
300	zzz	10/3/2015	test7
300	zzz	10/3/2015	test8
300	zzz	10/3/2015	test9

in the above table

in Text field test1 is the earliest value of ID 100,

test5 is the earliest value of ID 200,
test7 is the earliest value of ID 300,

we need to show earliest values for the above IDs as a reult.:

Could u pls help me to get the below result:

Result:

ID	Name	Date	Text
100	xxx	10/1/2015	test1
200	yyy	10/2/2015	test5
300	zzz	10/3/2015	test7

Re: Earliest Value

swuehl — Thu, 18 Feb 2016 17:00:34 GMT

How do you determine earliest value? Your Date values are the same per ID.

If you are referring to the first record in your table per ID, try

NOCONCATENATE

LOAD

ID, FirstValue(Name) as Name, FirstValue(Date) as Date, FirstValue(Text) as Text

RESIDENT YourTable

GROUP BY ID;

DROP TABLE YourTable;

Re: Earliest Value

Oleg_Troyansky — Thu, 18 Feb 2016 17:01:52 GMT

It looks like you need to aggregate your data with the GROUP BY load. SOmething like this:

LOAD

ID,

Name,

Date,

FirstSortedValue(Text)

RESIDENT

DetailedTable

GROUP BY

ID,

Name,

Date

;

You may have to sort your data in the desired order before grouping, or play with the optional sort-weight parameter of the FirstSortedValue() function.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!