Number Sequence

[Anonymous]

Hello,

I have Program No and Date ,I want sequence to be created.

Sequence should be created for each Program No and Ascending order of date

Program No        Date        Sequence
30                 04-Apr-2011        1
40                 04-Apr-2011        1
70                04-Apr-2011        1
80                01-Apr-2011        1
80                02-Apr-2011        2
80                03-Apr-2011        3
80                04-Apr-2011        4
100               04-Apr-2011        1
110               05-Apr-2011        1
160              05-Apr-2011        1
170              04-Apr-2011        1
170               05-Apr-2011        2
170              06-Apr-2011        3
180              07-Apr-2011        1
190              06-Apr-2011        1
200              07-Apr-2011        1

201               30-Apr-2011       1

201               1-May-2011       2

Thanks

[sunny_talwar]

Try this script:

Table:

LOAD [Program No],

  Date#(Date, 'DD-MMM-YYYY') as Date;

LOAD * Inline [

Program No,        Date

30,                 04-Apr-2011

40,                04-Apr-2011

70,                04-Apr-2011

80,                01-Apr-2011

80,                02-Apr-2011

80,                03-Apr-2011

80,                04-Apr-2011

100,               04-Apr-2011

110,               05-Apr-2011

160,              05-Apr-2011

170,              04-Apr-2011

170,               05-Apr-2011

170,              06-Apr-2011

180,              07-Apr-2011

190,              06-Apr-2011

200,              07-Apr-2011

201,               30-Apr-2011

201,               1-May-2011

];

FinalTable:

LOAD [Program No],

  Date,

  If([Program No] = Peek('Program No'), 1 + Peek('Sequence'), 1) as Sequence

Resident Table

Order By [Program No], Date;

DROP Table Table;

Output:

[mohammadkhatimi]

Use order by clause

First convert date into numeric and the sort date by using order by ASC..

Hope this will helps you..!!

Regards,

Mohammad

[senpradip007]

Have a look at the attachment. Hope it will help.

[MarcoWedel]

Left Join (yourtable)

Load distinct

      ProgramNo,

      Date,

      AutoNumber(Date, ProgramNo) as Sequence

Resident yourtable

Order by Date;

[Anonymous]

Thank you sunny its work

[sunny_talwar]

Great,

I am glad I was able to help.

Best,

Sunny

[MK_QSL]

Technically looking, all solutions are working but I can say Marco has given the best and simple way of creating sequence.

[sunny_talwar]

That's why you guys are experts Manish. We can offer solutions, but to offer efficient solutions like you experts, we have a long way to go.

[MK_QSL]

MarcoWedel‌  is always providing an efficient way of working. His scripting knowledge is appreciable.

sunindia‌ everyone here is a learner !