Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
markgraham123
Contributor II

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
Honored Contributor

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

Try this:

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

8 Replies
stabben23
Honored Contributor

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

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

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
Valued Contributor III

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

Hi,

You can accomplish this in a straight table as well:

Capture.PNG

Capture.PNG

See the attached qvw.

Hope this helps.

MVP
MVP

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



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
Contributor II

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

Thanq max...

It Worked

markgraham123
Contributor II

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

Thank you verymuch sinan..

It worked.

markgraham123
Contributor II

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

Thank very much my brother..

All the solutions worked

markgraham123
Contributor II

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

Thanq staffan...

It worked

Community Browser