topic Re: Calculate row number for groups in QlikView

Calculate row number for groups

vishnus85 — Mon, 05 Mar 2018 22:35:52 GMT

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

Re: Calculate row number for groups

vishsaggi — Mon, 05 Mar 2018 22:58:50 GMT

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;

Re: Calculate row number for groups

juraj_misina — Mon, 05 Mar 2018 22:59:14 GMT

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

Re: Calculate row number for groups

juraj_misina — Mon, 05 Mar 2018 23:01:04 GMT

OMG, this is so elegant.

Re: Calculate row number for groups

vishnus85 — Tue, 06 Mar 2018 06:02:52 GMT

Perfect. Thanks Viswarath.