topic Re: Pick the top one in the table among two values in QlikView

Pick the top one in the table among two values

markgraham123 — Mon, 21 Dec 2015 15:50:52 GMT

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!

Re: Pick the top one in the table among two values

stabben23 — Mon, 21 Dec 2015 18:30:15 GMT

Try this:

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

Re: Pick the top one in the table among two values

sunny_talwar — Mon, 21 Dec 2015 18:34:04 GMT

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

];

Re: Pick the top one in the table among two values

sinanozdemir — Mon, 21 Dec 2015 18:37:33 GMT

Hi,

You can accomplish this in a straight table as well:

See the attached qvw.

Hope this helps.

Re: Pick the top one in the table among two values

maxgro — Mon, 21 Dec 2015 18:39:20 GMT

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;

Re: Pick the top one in the table among two values

markgraham123 — Mon, 21 Dec 2015 19:32:40 GMT

Thanq max...

It Worked

Re: Pick the top one in the table among two values

markgraham123 — Mon, 21 Dec 2015 19:33:07 GMT

Thank you verymuch sinan..

It worked.

Re: Pick the top one in the table among two values

markgraham123 — Mon, 21 Dec 2015 19:33:41 GMT

Thank very much my brother..

All the solutions worked

Re: Pick the top one in the table among two values

markgraham123 — Mon, 21 Dec 2015 19:33:58 GMT

Thanq staffan...

It worked