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: 
sunilkumarqv
Specialist II
Specialist II

Get max record of the each ID

HI all ,

Please help me to get output table based on inpt table below

Input table

ID,Prodcut,Rowno

1,A,1

1,A,2

2,B,1

3,C,1

4,D,1

4,D,2

4,D,3

5,E,1

Get latest rowno of the each ID below likw this

for

output:

ID,Product,Rowno

1,A,2

2,B,1

3,C,1

4,D,3

5,E,1

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table:

LOAD * Inline [

ID,Prodcut,Rowno

1,A,1

1,A,2

2,B,1

3,C,1

4,D,1

4,D,2

4,D,3

5,E,1

];

Right Join (Table)

LOAD ID,

  Max(Rowno) as Rowno

Resident Table

Group By ID;


Capture.PNG

View solution in original post

14 Replies
sunny_talwar

Try this:

Table:

LOAD * Inline [

ID,Prodcut,Rowno

1,A,1

1,A,2

2,B,1

3,C,1

4,D,1

4,D,2

4,D,3

5,E,1

];

Right Join (Table)

LOAD ID,

  Max(Rowno) as Rowno

Resident Table

Group By ID;


Capture.PNG

ali_hijazi
Partner - Master II
Partner - Master II

output:

load ID, Product,max(Rowno) as Rowno

resident Input

group by ID, Product

I can walk on water when it freezes
sunilkumarqv
Specialist II
Specialist II
Author

Thanks sunny ,

realy helpful but have one more help

have get the count of each product  for the id of the latest Rowno just like that out put

to show in a bar chart

Product as dimension and expression : count(distinct ID)

where as each product has latest rowno

MayilVahanan

Hi

Try like this

Load ID, Prodcut,Max(Rowno) as Rowno

from yourInputfile

group by ID, Prodcut

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
sunny_talwar

So what's wrong? The chart isn't working?

awhitfield
Partner - Champion
Partner - Champion

Hi sunil,

this will work:

Data:
Load * inline
[
ID,Prodcut,RowNo

1,A,1
1,A,2
2,B,1
3,C,1
4,D,1
4,D,2
4,D,3
5,E,1
]
;

Data2:
NoConcatenate Load
ID,
Prodcut,
Max(RowNo) as RowNo
Resident Data
Group by ID, Prodcut;

Drop Table Data;

HTH Andy

sunilkumarqv
Specialist II
Specialist II
Author

Hi sunny ,

NUMBERDATESTATUSFlag
129/12/2015 :Assigned0
129/12/2015 :Assigned0
129/12/2015 :Resolved1
215/02/2016 :Assigned0
215/02/2016 :In Progress1
304/02/2016 :Pending0
304/02/2016 :Assigned0
304/02/2016 :In Progress0
304/02/2016 :Resolved1
425/02/2016 :Pending0
425/02/2016 :Assigned0
425/02/2016 :In Progress0
425/02/2016 :Resolved1
526/02/2016 :Assigned0
526/02/2016 :In Progress1

please help me to create flag column like above table

sunilkumarqv
Specialist II
Specialist II
Author

HI Andrew,

Thanks for your help

first have to create Rowno based on the ID and Prodcut fields like mentioned table then your script will work

please tell me how to create a Rowno first in scrip

sunilkumarqv
Specialist II
Specialist II
Author

Hi myile ,

before that have to create Rowno please suggest