Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Get max record of the each ID

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

14 Replies

Re: Get max record of the each ID

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

Re: Get max record of the each ID

output:

load ID, Product,max(Rowno) as Rowno

resident Input

group by ID, Product

sunilkumarqv
Valued Contributor II

Re: Get max record of the each ID

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

Re: Get max record of the each ID

Hi

Try like this

Load ID, Prodcut,Max(Rowno) as Rowno

from yourInputfile

group by ID, Prodcut

Re: Get max record of the each ID

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

awhitfield
Esteemed Contributor

Re: Get max record of the each ID

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

Re: Get max record of the each ID

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

Re: Get max record of the each ID

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

Re: Get max record of the each ID

Hi myile ,

before that have to create Rowno please suggest

Community Browser