topic Re: Row Number Help in QlikView

Row Number Help

Wed, 24 Jun 2015 10:04:47 GMT

Hi Experts

I am using Rowno() in my Script as Rank

I want to get an output of the following

How Can I Achieve this Please Help

Branch	cutomer	Month	Rank	TSales	Expected Rank
1	1101	Jan 2015	1	2251	1
1	1101	Feb 2015	2	2282	2
1	1101	Mar 2015	3	2310	3
1	1101	Apr 2015	4	2341	4
2	1102	Jan 2015	5	2252	5
2	1102	Feb 2015	6	2283	1
2	1102	Mar 2015	7	2311	2
2	1102	Apr 2015	8	2342	3
3	1103	Jan 2015	9	2253	1
3	1103	Feb 2015	10	2284	2
3	1103	Mar 2015	11	2312	3
3	1103	Apr 2015	12	2343	4
4	1104	Jan 2015	13	2254	1
4	1104	Feb 2015	14	2285	2
4	1104	Mar 2015	15	2313	3
4	1104	Apr 2015	16	2344	4
5	1105	Jan 2015	17	2255	1
5	1105	Feb 2015	18	2286	2
5	1105	Mar 2015	19	2314	3
5	1105	Apr 2015	20	2345	4

Re: Row Number Help

swuehl — Wed, 24 Jun 2015 10:07:48 GMT

Try autonumber

LOAD Branch,

customer,

Month,

TSales,

Autonumber(recno(), customer) as Rank,

...

FROM ...;

Re: Row Number Help

sunny_talwar — Wed, 24 Jun 2015 10:10:47 GMT

Try this script:

Table:

LOAD Branch,

cutomer,

Month,

Rank,

TSales

FROM

[https://community.qlik.com/thread/169775]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD *,

Alt(If(Branch = Peek('Branch') and cutomer = Peek('cutomer'), Peek('New Rank') + 1), 1) as [New Rank]

Resident Table

Order By Branch, cutomer, TSales;

DROP Table Table;

Re: Row Number Help

Anonymous — Wed, 24 Jun 2015 10:15:50 GMT

load the following

autonumber(Recno(),Branch) or autonumber(Recno(), Customer)

either one works

Re: Row Number Help

Kushal_Chawda — Wed, 24 Jun 2015 10:20:35 GMT

You can try something like this

LOAD *,AutoNumber(Month,Branch) as RANK Inline [

Branch, cutomer, Month, Rank, Sales

1, 1101, Jan 2015, 1, 2251

1, 1101, Feb 2015, 2, 2282

1, 1101, Mar 2015, 3, 2310

1, 1101, Apr 2015, 4, 2341

2, 1102, Jan 2015, 5, 2252

2, 1102, Feb 2015, 6, 2283

2 ,1102, Mar 2015, 7, 2311

2, 1102, Apr 2015, 8, 2342

3, 1103, Jan 2015, 9, 2253

3, 1103, Feb 2015, 10, 2284

3 ,1103, Mar 2015, 11, 2312

3 ,1103, Apr 2015, 12, 2343

4, 1104, Feb 2015, 14, 2285

4, 1104, Mar 2015, 15, 2313

4 ,1104, Apr 2015, 16, 2344

5 ,1105, Jan 2015, 17, 2255

5, 1105, Feb 2015, 18, 2286

5, 1105, Mar 2015, 19, 2314

5, 1105, Apr 2015, 20, 2345];

Re: Row Number Help

sasiparupudi1 — Wed, 24 Jun 2015 11:31:14 GMT

I think you will have to convert your date format to achieve the desired results

LOAD *,Date(Date#(Month,'MMM YYYY'),'YYYYMM') as DateString Inline [

Branch, cutomer, Month, Rank, Sales

1, 1101, Jan 2015, 1, 2251

1, 1101, Feb 2015, 2, 2282

1, 1101, Mar 2015, 3, 2310

1, 1101, Apr 2015, 4, 2341

2, 1102, Jan 2015, 5, 2252

2, 1102, Feb 2015, 6, 2283

2 ,1102, Mar 2015, 7, 2311

2, 1102, Apr 2015, 8, 2342

3, 1103, Jan 2015, 9, 2253

3, 1103, Feb 2015, 10, 2284

3 ,1103, Mar 2015, 11, 2312

3 ,1103, Apr 2015, 12, 2343

4, 1104, Feb 2015, 14, 2285

4, 1104, Mar 2015, 15, 2313

4 ,1104, Apr 2015, 16, 2344

5 ,1105, Jan 2015, 17, 2255

5, 1105, Feb 2015, 18, 2286

5, 1105, Mar 2015, 19, 2314

5, 1105, Apr 2015, 20, 2345];

y1:

LOAD *,

AutoNumber(Month,Branch) as [New Rank]

Resident y

Order By Branch, cutomer,DateString ;

DROP Table y;

hope this helps

Re: Row Number Help

sasiparupudi1 — Wed, 08 Jul 2015 06:53:58 GMT

Did you come alright with your problem?