Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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!