Seq_No

[upaliwije]

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

[Gysbert_Wassenaar · Accepted solution]

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

[marcus_sommer]

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

[avinashelite]

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

[Kushal_Chawda]

Create the field as

autonumber(CLAIM_NO,POLICY_NO) as SeqNo

[jonathandienst]

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