topic Rowno() per unique field? in QlikView

Rowno() per unique field?

Wed, 27 Aug 2014 11:03:16 GMT

Good afternoon,

I am hoping someone may be able to help with the following problem I have encountered and I am grateful for any help you may be able to provide.

The problem:

I need to count the number of occurrences a policy number appears in a claims report, however I need each occurrence of the policy number to be numbered sequentially from 1 upwards.

The date looks like this:

ClaimsReport:

LOAD

Policy_Num,

Claim_Num,

ClaimCost

From ;

For example I need a line of script that can create the field 'Policy_Claim_Count'

Policy_Num	Claim_Num	Policy_Claim_Count
POL_001	Claim_006	1
POL_002	Claim_009	1
POL_003	Claim_001	1
POL_003	Claim_002	2
POL_004	Claim_003	1
POL_005	Claim_005	1
POL_006	Claim_004	1
POL_006	Claim_007	2
POL_006	Claim_008	3

Thanks for any help

Gary

Re: Rowno() per unique field?

m_woolf — Wed, 27 Aug 2014 11:06:32 GMT

http://community.qlikview.com/blogs/qlikviewdesignblog/2013/09/16/counters-in-the-load

Re: Rowno() per unique field?

MK_QSL — Wed, 27 Aug 2014 11:07:26 GMT

Temp:

Load * Inline

[

Policy_Num, Claim_Num

POL_001, Claim_006

POL_002, Claim_009

POL_003, Claim_001

POL_003, Claim_002

POL_004, Claim_003

POL_005, Claim_005

POL_006, Claim_004

POL_006, Claim_007

POL_006, Claim_008

];

NoConcatenate

Load

Policy_Num,

Claim_Num,

If(Policy_Num = Peek(Policy_Num), RangeSum(Peek(Policy_Claim_Count),1),1) as Policy_Claim_Count

Resident Temp

Order By Policy_Num,Claim_Num;

Drop Table Temp;

Re: Rowno() per unique field?

MarcoWedel — Wed, 27 Aug 2014 11:07:28 GMT

AutoNumber(RowNo(), Policy_Num)

Re: Re: Rowno() per unique field?

MarcoWedel — Wed, 27 Aug 2014 11:12:09 GMT

LOAD Policy_Num, 
     Claim_Num,
     AutoNumber(RowNo(), Policy_Num) as Policy_Claim_Count
FROM [http://community.qlik.com/thread/130955] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Rowno() per unique field?

Wed, 27 Aug 2014 11:14:49 GMT

Thanks Marco.

I knew it wouldn't be difficult for someone who knows what they are doing.

Cheers

Gary