Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Number Sequence

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

12 Replies
Highlighted

Re: Number Sequence

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:

Capture.PNG

Highlighted
Partner
Partner

Re: Number Sequence

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

Highlighted
Specialist III
Specialist III

Re: Number Sequence

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

Highlighted

Re: Number Sequence

Left Join (yourtable)

Load distinct

      ProgramNo,

      Date,

      AutoNumber(Date, ProgramNo) as Sequence

Resident yourtable

Order by Date;

Highlighted
Contributor III
Contributor III

Re: Number Sequence

Thank you sunny its work

Highlighted

Re: Number Sequence

Great,

I am glad I was able to help.

Best,

Sunny

Highlighted
MVP
MVP

Re: Number Sequence

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

Highlighted

Re: Number 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.

Highlighted
MVP
MVP

Re: Number Sequence