topic Re: How to generate sequence number on two arguments in QlikView

How to generate sequence number on two arguments

Sat, 03 May 2014 14:10:50 GMT

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.

Re: How to generate sequence number on two arguments

swuehl — Sat, 03 May 2014 14:19:40 GMT

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

LOAD

ClientID,

ContractDate,

autonumber(ContractDate, ClientID) as ContractNbr

FROM ...

Re: How to generate sequence number on two arguments

tresB — Sat, 03 May 2014 14:22:22 GMT

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

]

Re: How to generate sequence number on two arguments

its_anandrjs — Sat, 03 May 2014 14:31:38 GMT

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

]

Re: How to generate sequence number on two arguments

Sat, 03 May 2014 14:40:15 GMT

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.

Re: How to generate sequence number on two arguments

SunilChauhan — Sat, 03 May 2014 15:00:48 GMT

in expression use

Rowno()

in dimension use

ClientID and ContractDate

see the attched file