topic Re: Assign serial number after sorting in QlikView

Assign serial number after sorting

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to sort the column and then assign serial number

example I want to sort below data based on LOCN and then assign serial number, expected data shown below

Data:

LOCN	RTN
1A1A	1023
1B2A	4231
1A1A	1028
1A2A	3289
1B3A	1628
1A3A	3290
1A4A	2111
1A2A	3123
1A4A	2312
1B1A	4421
1A2A	3124
1B1B	1245
1B1B	1287
1A1A	1027
1B2B	7652

Expected Data:

Serial No	LOCN	RTN
1	1A1A	1023
2	1A1A	1027
3	1A1A	1028
4	1A2A	3123
5	1A2A	3124
6	1A2A	3289
7	1A3A	3290
8	1A4A	2111
9	1A4A	2312
10	1B1A	4421
11	1B1B	1245
12	1B1B	1287
13	1B2A	4231
14	1B2B	7652
15	1B3A	1628

Re: Assign serial number after sorting

tresB — Fri, 30 Sep 2016 05:01:29 GMT

Try like:

Data:

Load * Inline [

LOCN RTN

1A1A 1023

1B2A 4231

1A1A 1028

1A2A 3289

1B3A 1628

1A3A 3290

1A4A 2111

1A2A 3123

1A4A 2312

1B1A 4421

1A2A 3124

1B1B 1245

1B1B 1287

1A1A 1027

1B2B 7652] (delimiter is '\t');

Final:

Load

RowNo() as [Serial No],

*

Resident Data order by LOCN;

DROP Table Data;

Re: Assign serial number after sorting

susovan — Fri, 30 Sep 2016 06:17:31 GMT

You also try this,

Table1:

LOAD * INLINE [

LOCN,RTN

1A1A,1023

1B2A,4231

1A1A,1028

1A2A,3289

1B3A,1628

1A3A,3290

1A4A,2111

1A2A,3123

1A4A,2312

1B1A,4421

1A2A,3124

1B1B,1245

1B1B,1287

1A1A,1027

1B2B,7652 ];

left join

Table2:

LOAD *,

RowNo() as Sr.no

Resident Table1 Order by LOCN;

Re: Assign serial number after sorting

Anonymous — Fri, 30 Sep 2016 06:36:31 GMT

Hi Vibhu,

Use this code if you want same Serial Number for Locn field

Left Join (YourTable)

LOAD *,

AutoNumber(LOCN) as Serial

RESIDENT YourTable

ORDER BY LOCN ASC;

If you want one serial number for each row:

Left Join (YourTable)

LOAD *,

RowNo() as Serial

RESIDENT YourTable

ORDER BY LOCN ASC;

If you want one serial number for each combination of Locn and Rtn:

Left Join (YourTable)

LOAD *,

Autonumber(LOCN&'-'&RTN) as Serial

RESIDENT YourTable

ORDER BY LOCN ASC, RTN ASC;

Regards!!

Re: Assign serial number after sorting

antoniotiman — Fri, 30 Sep 2016 07:07:19 GMT

Hi Vibhu,

Temp

LOAD * From Table;

Load *,

RowNo() as SerialNo

Resident Temp

Order By LOCN,RTN;

Drop Table Temp;

Regards,

Antonio