Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Earliest Value

T1:

   

IDNameDateText
100xxx10/1/2015test1
100xxx10/1/2015test2
200yyy10/2/2015test5
200yyy10/2/2015test6
300zzz10/3/2015test7
300zzz10/3/2015test8
300zzz10/3/2015test9

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:

   

IDNameDateText
100xxx10/1/2015test1
200yyy10/2/2015test5
300zzz10/3/2015test7
2 Replies
swuehl
MVP
MVP

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;

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!