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: 
markgraham123
Specialist
Specialist

Pick the top one in the table among two values

Hi all,

I was trying to fetch the Dept.s of the names.

Every name will have one Dept. and more than one ID.

For eg: in the attached app. we have Name "B" with Dept. J+ JP.

Since we have two different IDs 2 and 4 for the same name, we can see redundancy of the same record.


ID,Name,Dept.
1,A,HD
2,B,J + JP
3,C,JB
4,B,J + JP
5,D,DD
6,E,HDLA
7,E,HDLA
8,F,GW
9,G,DHJJLW

Required O/P:

I would like to fetch the values in an unique manner, where i bring in only first values and ignore if the record is available again after the first record. (I tried "Peek" but no luck ) - "Peek('Dept.', 0, 'Table1') as DeptFinal"

I was trying to get some thing like:

ID,Name,Dept.
1,A,HD
2,B,J + JP
3,C,JB
5,D,DD
6,E,HDLA
8,F,GW
9,G,DHJJLW

It would be great help, if someone help!

1 Solution

Accepted Solutions
stabben23
Partner - Master
Partner - Master

Try this:

inner Join LOAD
min(ID) as ID, Name, Dept.
Resident Table1
group by Name, Dept.;

View solution in original post

8 Replies
stabben23
Partner - Master
Partner - Master

Try this:

inner Join LOAD
min(ID) as ID, Name, Dept.
Resident Table1
group by Name, Dept.;

sunny_talwar

Or this:

Table1:

LOAD FirstValue(ID) as ID,

  Name,

  Dept.

Group By Name, Dept.;

LOAD * Inline

[

ID, Name, Dept.

1, A, HD

2, B, J + JP

3, C, JB

4, B, J + JP

5, D, DD

6, E, HDLA

7, E, HDLA

8, F, GW

9, G, DHJJLW

];


Capture.PNG

sinanozdemir
Specialist III
Specialist III

Hi,

You can accomplish this in a straight table as well:

Capture.PNG

Capture.PNG

See the attached qvw.

Hope this helps.

maxgro
MVP
MVP



Table1:

LOAD * Inline

[

ID, Name, Dept.

1, A, HD

2, B, J + JP

3, C, JB

4, B, J + JP

5, D, DD

6, E, HDLA

7, E, HDLA

8, F, GW

9, G, DHJJLW

];

Table2:

NoConcatenate LOAD *

Resident Table1

Where Peek('Dept.') <> [Dept.]

order by [Dept.], ID;

DROP Table Table1;

markgraham123
Specialist
Specialist
Author

Thanq max...

It Worked

markgraham123
Specialist
Specialist
Author

Thank you verymuch sinan..

It worked.

markgraham123
Specialist
Specialist
Author

Thank very much my brother..

All the solutions worked

markgraham123
Specialist
Specialist
Author

Thanq staffan...

It worked