Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
output:
load ID, Product,max(Rowno) as Rowno
resident Input
group by ID, Product
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
Hi
Try like this
Load ID, Prodcut,Max(Rowno) as Rowno
from yourInputfile
group by ID, Prodcut
So what's wrong? The chart isn't working?
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
Hi sunny ,
NUMBER | DATE | STATUS | Flag |
1 | 29/12/2015 : | Assigned | 0 |
1 | 29/12/2015 : | Assigned | 0 |
1 | 29/12/2015 : | Resolved | 1 |
2 | 15/02/2016 : | Assigned | 0 |
2 | 15/02/2016 : | In Progress | 1 |
3 | 04/02/2016 : | Pending | 0 |
3 | 04/02/2016 : | Assigned | 0 |
3 | 04/02/2016 : | In Progress | 0 |
3 | 04/02/2016 : | Resolved | 1 |
4 | 25/02/2016 : | Pending | 0 |
4 | 25/02/2016 : | Assigned | 0 |
4 | 25/02/2016 : | In Progress | 0 |
4 | 25/02/2016 : | Resolved | 1 |
5 | 26/02/2016 : | Assigned | 0 |
5 | 26/02/2016 : | In Progress | 1 |
please help me to create flag column like above table
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
Hi myile ,
before that have to create Rowno please suggest