Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dmpilars
New Contributor II

Generate counter by groups

Hi People!

Please Help me, I try to make a consecutive number like this:

COD               NIT             Consecutive

00000          123456          000001

00000          123456          000001

00000          123456          000001

00000          123456          000001

00000          234567          000002

00000          234567          000002

00000          234567          000002

00000          234567          000002

00009          345678          000003

00009          345678          000003

00010          345678          000003

00010          345678          000003

00011          345678          000003


I need to assign the consecutive number and this depends on NIT and COD value.

1 Solution

Accepted Solutions
MVP
MVP

Re: Generate counter by groups

Check this script

Table:

LOAD RowNo() as RowNum,

*,

Num(AutoNumber([NIT&COD1], Left([NIT&COD1], 7)), '000000') as CONSEC;

LOAD * INLINE [

    NIT&COD1

    500096929000000

    500096929000000

    500096929000000

    500096929000001

    500096929000001

    500096929000001

    500096929000002

    500096929000002

    500096929000002

    500096929000009

    500096929000009

    500096929000009

    500122959000955

    500122959000955

    500122959000955

    500129157000959

    500129157000959

    500129157000959

    500129157000960

    500129157000960

    500129157000960

    500150250000012

    500150250000012

    500150250000012

];

Capture.PNG

12 Replies
MVP
MVP

Re: Generate counter by groups

It seems to be only dependent on NIT... May be this

Num(AutoNumber(NIT), '000000') as Consecutive

zebhashmi
Valued Contributor

Re: Generate counter by groups

I would say

Hash128(COD,NIT) as Consecutive,

omarbensalem
Esteemed Contributor

Re: Generate counter by groups

as I see it's only based on NIT no?

and do u want it on the script or in a table? without altering the script?

dmpilars
New Contributor II

Re: Generate counter by groups

My calculation conditions changes. Now I need to make this:

COD            NIT&COD1     CONSEC

00009          123450001     000001

00009          123450001     000002

00009          123450001     000003

00009          123450002     000001

00009          123450002     000002   

00009          123450003     000001   

00009          123450004     000002

00010          456780001     000001

00010          456780002     000001  

00010          456780002     000002

How I can enumerate in this form?

dmpilars
New Contributor II

Re: Generate counter by groups

I need to do that in the script

MVP
MVP

Re: Generate counter by groups

This

Num(AutoNumber(RowNo(), COD&NIT&COD1), '000000') as CONSEC

dmpilars
New Contributor II

Re: Generate counter by groups

                        NIT&COD1               CONSEC

50009692900000000001
50009692900000000001
50009692900000000001
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000200003
50009692900000200003
50009692900000200003
50009692900000200003
50009692900000200003
50009692900000200003
50009692900000200003
50009692900000900004
50009692900000900004
50009692900000900004
50009692900000900004
50009692900000900004
50009692900000900004
50009692900000900004
50009692900000900004
50009692900000900004
50012295900095500001
50012295900095500001
50012295900095500001
50012295900095500001
50012295900095500001
50012295900095500001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700096000002
50012915700096000002
50012915700096000002
50012915700096000002
50012915700096000002
50012915700096000002
50012915700096000002
50015025000001200001
50015025000001200001
50015025000001200001
50015025000001200001
50015025000001200001

And, have you any idea for this format?

MVP
MVP

Re: Generate counter by groups

Not sure I understand the question? Can you elaborate?

dmpilars
New Contributor II

Re: Generate counter by groups

In this case I need a consecutive code that correspond with NIT&COD1 but when NIT&COD1 changue, the consecutive start in 00001 again. This data is in groups.

NIT( first 9 characters); COD1(next 6 characters);  COD1 define a new group on master group defined by NIT. When NIT changues The consecutive should start in 00001 again.

NIT&COD1                  CONSEC

50009692900000000001
50009692900000000001
50009692900000000001
50009692900000100002
50009692900000100002
50009692900000100002
50009692900000200003
50009692900000200003
50009692900000200003
50009692900000900004
50009692900000900004
50009692900000900004
50012295900095500001
50012295900095500001
50012295900095500001
50012915700095900001
50012915700095900001
50012915700095900001
50012915700096000002
50012915700096000002
50012915700096000002
50015025000001200001
50015025000001200001
50015025000001200001