Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Partner
Partner

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.

Capture.PNG

Attaching the sample sheet. Can anyone help me to calculate this in (1) script side  (2) UI using set analysis

Regards

Vishnu S

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Calculate row number for groups

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;

4 Replies
vishsaggi
Esteemed Contributor III

Re: Calculate row number for groups

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;

Highlighted
Luminary
Luminary

Re: Calculate row number for groups

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

Luminary
Luminary

Re: Calculate row number for groups

OMG, this is so elegant.

Partner
Partner

Re: Calculate row number for groups

Perfect. Thanks Viswarath.