topic Re: Row number based on the same value in QlikView

Row number based on the same value

lawrance — Wed, 07 Dec 2016 14:41:11 GMT

Hi,

Please help me to solve this problem.

I need output for below sample example

Date ID

12/01/2016 1

12/01/2016 2

12/01/2016 3

12/02/2016 1

12/02/2016 2

12/03/2016 1

12/04/2016 1

12/04/2016 2

12/04/2016 3

12/04/2016 4

Thanks,

Lawrance. A

Re: Row number based on the same value

sunny_talwar — Wed, 07 Dec 2016 14:42:52 GMT

May be like this:

LOAD Date,

AutoNumber(RowNo(), Date) as ID

FROM ....

Re: Row number based on the same value

sunny_talwar — Wed, 07 Dec 2016 14:43:26 GMT

or this:

LOAD Date,

AutoNumber(RowNo(), Floor(Date)) as ID

FROM ....

Re: Row number based on the same value

marcus_sommer — Wed, 07 Dec 2016 14:46:02 GMT

You could use a to date ordered resident load with:

load

Date,

if(Date = previous(Date), peek('ID') + 1, 1) as ID

resident YourTable order by Date;

- Marcus

Re: Row number based on the same value

Aurelien_Martinez — Wed, 07 Dec 2016 14:49:52 GMT

Hi,

LOAD

If(Date=Peek(Date), Peek(ID)+1, 1) as ID,

Date

INLINE [

Date

12/01/2016

12/02/2016

12/03/2016

12/04/2016

];

(Perhaps you need to do a order by Date)

Aurélien

Re: Row number based on the same value

lawrance — Tue, 13 Dec 2016 21:48:37 GMT

Hi,

Please give me solution for this,

I need Sequence number based on the category for selected date.

For Example,

Date ID Category

1/1/2016 1 connect

1/1/2016 2 connect

1/1/2016 3 connect

1/1/2016 1 Disconnect

1/1/2016 2 Disconnect

1/1/2016 3 Dicconnect

1/1/2016 1 close

1/1/2016 2 close

2/1/2016 1 connect

2/1/2016 2 connect

2/1/2016 3 connect

2/1/2016 1 Disconnect

2/1/2016 2 Disconnect

2/1/2016 3 Dicconnect

2/1/2016 1 close

2/1/2016 2 close

Re: Row number based on the same value

vishsaggi — Tue, 13 Dec 2016 22:30:29 GMT

Your Category has few incorrect spellings. Considering Marcus script you can use as below:

TEST:

LOAD * INLINE [

Date , Category

1/1/2016, connect

1/1/2016, Disconnect

1/1/2016, close

2/1/2016, connect

2/1/2016, Disconnect

2/1/2016, close

];

MISC:

LOAD *, RowNo() AS RowNum,

IF(Date = Previous(Date), Peek(ID)+1, 1) AS ID

Resident TEST

Order By Category,Date;

DROP Table TEST;