Row number based on the same value

[lawrance]

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

[sunny_talwar]

May be like this:

LOAD Date,

          AutoNumber(RowNo(), Date) as ID

FROM ....

[sunny_talwar]

or this:

LOAD Date,

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

FROM ....

[marcus_sommer]

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

[Aurelien_Martinez]

Hi,

LOAD

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

  Date

INLINE [

Date              

12/01/2016

12/01/2016

12/01/2016

12/02/2016

12/02/2016

12/03/2016

12/04/2016

12/04/2016

12/04/2016

12/04/2016

];

(Perhaps you need to do a order by Date)

Aurélien

[lawrance]

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

[vishsaggi · Accepted solution]

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,               connect

1/1/2016,               connect

1/1/2016,               Disconnect

1/1/2016,               Disconnect

1/1/2016,               Disconnect

1/1/2016,               close

1/1/2016,               close

2/1/2016,               connect

2/1/2016,               connect

2/1/2016,               connect

2/1/2016,               Disconnect

2/1/2016,               Disconnect

2/1/2016,               Disconnect

2/1/2016,               close

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;