Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Rowno() per unique field?

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_NumClaim_NumPolicy_Claim_Count

POL_001

Claim_006

1

POL_002Claim_0091
POL_003Claim_0011
POL_003Claim_0022
POL_004Claim_0031
POL_005Claim_0051
POL_006Claim_0041
POL_006Claim_0072
POL_006Claim_0083

Thanks for any help

Gary

Tags (3)
1 Solution

Accepted Solutions

Re: Rowno() per unique field?

AutoNumber(RowNo(), Policy_Num)

5 Replies
mwoolf
Honored Contributor II

Re: Rowno() per unique field?

Re: Rowno() per unique field?

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?

AutoNumber(RowNo(), Policy_Num)

Re: Re: Rowno() per unique field?

QlikCommunity_Thread_130955_Pic1.JPG.jpg

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

Not applicable

Re: Rowno() per unique field?

Thanks Marco.

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

Cheers

Gary

Community Browser