Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Is it possible to sort the values and get the first occurrence in Qlikview?
My requirement is this. I have to sort the names and one more column called Status in desc and get the first occurrence. Is this possible?
QNUm | Name | Status |
1000 | ABC | Yes |
1000 | ABC | No |
12000 | XYZ | No |
12000 | XYZ | No |
12000 | XYZ | Yes |
200 | DEF | N/A |
200 | DEF | Yes |
100 | IJK | Yes |
10 | KLM | No |
From this table i should get the following result:
QNUm | Name | Status |
1000 | ABC | Yes |
12000 | XYZ | Yes |
200 | DEF | Yes |
100 | IJK | Yes |
10 | KLM | No |
Is this possible?
Thanks,
Anupama Jagan
When you talk about Sorting? Why Status "No" is not available in the result?
In the script ?
Because for the QNum 10 and name KLM we have only one status - No..so that should be in the result..
Yes should be done in the script..
Try like:
t1:
Load * Inline [
QNUm, Name, Status
1000, ABC, Yes
1000, ABC, No
12000, XYZ, No
12000, XYZ, No
12000, XYZ, Yes
200, DEF, N/A
200, DEF, Yes
100, IJK, Yes
10, KLM, No]
;
NoConcatenate
t2:
Load
QNUm,
Name,
FirstValue(Status) as Status
Resident t1 group by QNUm, Name Order By Name asc, Status desc ;
Drop Table t1;
May be FirstSortedValue() for status in script?
Hi Tresesco,
I have a doubt in this..if i have more columns in the table what should be done?
Like i have few other fields like PNums, Dates etc should i add those to in group by?
Yes, may be both in group By and Order By.
But I need to sort by these two fields..