Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

Re: Earliest Value

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;

Highlighted
MVP & Luminary
MVP & Luminary

Re: Earliest Value

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!