Pick the top one in the table among two values

[markgraham123]

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!

[stabben23 · Accepted solution]

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

];

[sinanozdemir]

Hi,

You can accomplish this in a straight table as well:

See the attached qvw.

Hope this helps.

[maxgro]

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]

Thanq max...

It Worked

[markgraham123]

Thank you verymuch sinan..

It worked.

[markgraham123]

Thank very much my brother..

All the solutions worked

[markgraham123]

Thanq staffan...

It worked