How to generate sequence number on two arguments

[#1]

Hello, I'm new here and using QV on very basic level for now, hoping to learn more. Fighting with my table for 3 days and still cannot find the solution. I have a table with Client ID and Contract date, and I need to generate another column with number of contract for each client according to date.

ClientID          ContractDate     ContractNbr

AAA               01.01.2013                 1

AAA               01.02.2013                 2

AAA               01.03.2013                 3

BBB               01.01.2013                 1

BBB               01.02.2013                 2

BBB               01.03.2013                 3

First two columns I have, third column is the result I need to get.

[swuehl · Accepted solution]

You can either use some logic in your scipt involving peek() or previous(), or just

LOAD

     ClientID,

     ContractDate,

     autonumber(ContractDate, ClientID) as ContractNbr

FROM ...

[tresesco]

Load *,
If(Peek('ClientID')=ClientID, Peek('Number')+1,1) as Number
;

Load * Inline [
ClientID,ContractDate

AAA,01.01.2013

AAA,01.02.2013

AAA,01.03.2013

BBB,01.01.2013

BBB,01.02.2013

BBB,01.03.2013

]

[its_anandrjs]

You can write this way

LOAD

AutoNumber(ClientID,ContractDate) as Key1,

AutoNumberHash128(ClientID,ContractDate) as Key2

ClientID,ContractDate;

Load * Inline [

ClientID,ContractDate

AAA,01.01.2013

AAA,01.02.2013

AAA,01.03.2013

BBB,01.01.2013

BBB,01.02.2013

BBB,01.03.2013

]

[#5]

Thanks, swuehl answer definitely saved the rest of my weekend For the other solutions using Load * inline - do you mean I really need to put all rows in the script? If so - that doesn't work for me, as I have about 150 000 records in the table, just put here 6 for example. Thank you very much for your help.

[SunilChauhan]

in expression use

Rowno()

in dimension use

ClientID    and    ContractDate

see the attched file