topic Seq_No in QlikView

Seq_No

upaliwije — Wed, 11 Nov 2015 06:05:43 GMT

Hi Friends

I have following data table in my QV Document except seq No Column. I want to add SeqNo Col with the Seq No on the following basis

Under Each policy No seq No should be allocated to each claim in the order of Loss Date. Kindly help me to do this in my data load script

POLICY_NO	CLAIM_NO	LOSS_DATE	CLAIM_PAID	seqNo
1M00154F0000026	CL1M004F15000001	42103	18,300	1
1M00154F0000026	CL1M004F15000002	42117	25,273	2
3K00134F0000047	CL3K004F14000010	41975	11,580	1
3K00134F0000047	CL3K004F15000001	42010	29,722	2
3K00144F0000098	CL3K004F14000014	42000	10,232	1
3K00144F0000098	CL3K004F15000003	42117	17,175	2
3K00144F0000098	CL3K004F15000004	42149	42,750	3
8M00144F0000101	CL8M004F15000001	42005	15,420	1
8M00144F0000101	CL8M004F15000010	42083	34,000	2
8M00144F0000101	CL8M004F15000014	42099	21,260	3
8M00144F0000101	CL8M004F15000024	42204	16,752	4

Re: Seq_No

Gysbert_Wassenaar — Wed, 11 Nov 2015 06:19:34 GMT

Make sure your source data is sorted by POLICY_NO and LOSS_DATE. If it isn't the first created a table that reorders the data. Then you can calculate the seqNo field with autonumber(LOSS_DATE, POLICY_NO) as seqNo

Re: Seq_No

marcus_sommer — Wed, 11 Nov 2015 06:19:42 GMT

This could be done with peek() or previous() within a sorted Resident-Load, try something like this:

Load *,

if(rowno() =1 or peek('POLICY_NO') <> POLICY_NO, 1, seqNo + 1) as seqNo

Resident previousLoad Order by POLICY_NO, LOSS_DATE;

- Marcus

Re: Seq_No

avinashelite — Wed, 11 Nov 2015 06:19:47 GMT

Try like this :

1.First sort the table by LOASS_DATE

2.use AUTONUMBER(POLICY_NO,LOASS_DATE) as SeqNo

in the load script this will do

Re: Seq_No

Kushal_Chawda — Wed, 11 Nov 2015 06:43:27 GMT

Create the field as

autonumber(CLAIM_NO,POLICY_NO) as SeqNo

Re: Seq_No

jonathandienst — Wed, 11 Nov 2015 07:46:06 GMT

Please read the other answers before posting an answer that has already been provided.