Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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
Have a look at the attachment. Hope it will help.
Left Join (yourtable)
Load distinct
ProgramNo,
Date,
AutoNumber(Date, ProgramNo) as Sequence
Resident yourtable
Order by Date;
Thank you sunny its work
Great,
I am glad I was able to help.
Best,
Sunny
Technically looking, all solutions are working but I can say Marco has given the best and simple way of creating sequence.
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.