topic Re: Number Sequence in QlikView

Number Sequence

Anonymous — Fri, 14 Aug 2015 04:57:11 GMT

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

Re: Number Sequence

sunny_talwar — Fri, 14 Aug 2015 05:06:26 GMT

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:

Re: Number Sequence

mohammadkhatimi — Fri, 14 Aug 2015 05:06:34 GMT

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

Re: Number Sequence

senpradip007 — Fri, 14 Aug 2015 05:09:09 GMT

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

Re: Number Sequence

MarcoWedel — Fri, 14 Aug 2015 06:03:42 GMT

Left Join (yourtable)

Load distinct

ProgramNo,

Date,

AutoNumber(Date, ProgramNo) as Sequence

Resident yourtable

Order by Date;

Re: Number Sequence

Anonymous — Fri, 14 Aug 2015 06:07:31 GMT

Thank you sunny its work

Re: Number Sequence

sunny_talwar — Fri, 14 Aug 2015 06:12:51 GMT

Great,

I am glad I was able to help.

Best,

Sunny

Re: Number Sequence

MK_QSL — Fri, 14 Aug 2015 06:50:53 GMT

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

Re: Number Sequence

sunny_talwar — Fri, 14 Aug 2015 06:56:22 GMT

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.

Re: Number Sequence

MK_QSL — Fri, 14 Aug 2015 07:00:00 GMT

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

sunindia‌ everyone here is a learner !

Re: Number Sequence

sunny_talwar — Fri, 14 Aug 2015 07:02:41 GMT

Efficient solutions as well as alternative solutions for doing the same thing. I think he is one person I love seeing the response from just because they are so detailed. I have lately tried to copy his technique of providing as much details as possible in my responses.

Re: Number Sequence

MarcoWedel — Fri, 14 Aug 2015 07:46:08 GMT

thank you.

Most of what I learned, I learned in the Community, meaning from you also.

Qlik on

Marco

Re: Number Sequence

Fri, 14 Aug 2015 08:43:12 GMT

Hello Harinder,

Hope this will help you out.

Main:

Load * Inline [

ProgramNo,Dates

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

];

Tab2:

Load *,AutoNumber(ProgramNo&Dates,ProgramNo) as Sequence Resident Main

Order by ProgramNo,Dates;

Drop Table Main;