

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate row number for groups
Hi All,
I have the following table (only the fields in black) and in qlikview script, I want to stamp a value GROUP_ROWNO which lets me know the Nth occurrence of MPATID based on the ascending order of CREATEDT.
Attaching the sample sheet. Can anyone help me to calculate this in (1) script side (2) UI using set analysis
Regards
Vishnu S
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this in your UI
using straight table add
Dimensions:
MPATID, CASENUM, CREATEDT
Expression:
= Sum(Aggr(RowNo(), MPATID, CREATEDT))
Try this in your Script:
GetRow:
LOAD MPATID,
CASENUM,
CREATEDT
FROM
[..\..\Downloads\DummyDataMicheal1.xlsx]
(ooxml, embedded labels, table is MicTest);
LEFT JOIN(GetRow)
Final:
LOAD MPATID,
CREATEDT,
IF(MPATID <> Previous(MPATID), 1, Peek('GroupRow')+1) AS GroupRow
Resident GetRow
Order By MPATID, CREATEDT;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this in your UI
using straight table add
Dimensions:
MPATID, CASENUM, CREATEDT
Expression:
= Sum(Aggr(RowNo(), MPATID, CREATEDT))
Try this in your Script:
GetRow:
LOAD MPATID,
CASENUM,
CREATEDT
FROM
[..\..\Downloads\DummyDataMicheal1.xlsx]
(ooxml, embedded labels, table is MicTest);
LEFT JOIN(GetRow)
Final:
LOAD MPATID,
CREATEDT,
IF(MPATID <> Previous(MPATID), 1, Peek('GroupRow')+1) AS GroupRow
Resident GetRow
Order By MPATID, CREATEDT;
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
In script it would be something like this:
LOAD
MPATID,
CASENUM,
CREATEDT,
If(MPATID=Peek(MPATID), Peek(GROUP_ROWNO)+1, 1) as GROUP_ROWNO
FROM Source
Order By
MPATID,
CREATEDT
;
For chart expression you could try playing with Rank() but it is a bit tricky:
Rank(-CREATEDT)
Hope this helps.
Juraj
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
OMG, this is so elegant.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perfect. Thanks Viswarath.
