topic Re: Using autonumber to have repeating values in QlikView

Using autonumber to have repeating values

BSCIknowlec — Wed, 17 Feb 2021 11:33:02 GMT

hi experts,

im trying use the autonumber function to have a number for each record. the catch is that need to have this new field have repeating value for each of my batch number.

im trying to figure out what is the best combination of my fields to correctly get this?

i have 2 different batch numbers(just for this example, in my real app there will be countless amounts of batches), within each batch are a varying amount of samples, each sample has its own time/date stamp, value, and PK(1613494718,16134947191613494722 etc..)

lets say for batch 1 there are 20 samples, and for batch 2 there are 15,

what i need to generate is a new field(using autonumberi beleive is the best route?) to have number 1-20(due to 20 being the largest sample number), with autonumber value 1 being for the first value in both batches. i will then use expressions for my line graph to have 2 lines over each other for each batch to display performance.

i can get a line graph for each batch but i cant get it go over each other, only when one line finishes the next one starts,

any ideas on what is the best way to do this?

thanks

Re: Using autonumber to have repeating values

Saravanan_Desingh — Wed, 17 Feb 2021 19:53:20 GMT

Please post sample data and expected output

Re: Using autonumber to have repeating values

haskia — Wed, 17 Feb 2021 20:27:53 GMT

I believe what you are looking for is the RowNo() -or maybe even RecNo() function. It will add an increasing number for each row starting with 1.

Re: Using autonumber to have repeating values

BSCIknowlec — Thu, 18 Feb 2021 09:00:18 GMT

Hi @Saravanan_Desingh

here is an example for my two batches

both batches have the same fields, these are called subgroupID, date(this is a date & time field), a batch number and values.

so in batch one there are 20 samples, so therefore there are 20 subgroup ID's, 20 date/time fields, one batch number and 20 values

for batch two there are 15 samples so the same as batch one but 15 and not twenty.

the below screenshot is what i want. where count 1 = the first sample in both batches, therefore ill be able to do a line graph chart like below.

Re: Using autonumber to have repeating values

BSCIknowlec — Thu, 18 Feb 2021 09:02:26 GMT

hi @haskia thanks for your reply,

i tried that but all that does is give me a total count of my rows, what i need is only a count of the rows within a batch and not to have them unique,. that means that row #1 would equal the first sample in all my batches and not just one batch.

i explained it to @Saravanan_Desingh above, hopefully it makes sense.

if you have any ideas it would be much appreciated 🙂

Re: Using autonumber to have repeating values

Mr_H_Huang — Fri, 20 Oct 2023 15:45:48 GMT

You may try to use syntax MOD. Below is what I have done to relpeate a rolling autonumber for a period of Date. working_date is the date from a master calander, RWP_START_DT is when the cycle start date, RWP_CYCL_LEN is the length of the cycle for example 7 days, 30 days per cycle. The result day_number_MOD create a number order from 1 to the end of cycle 7 or 30 depends on the cycle days. You don't need to use interval you may use something else NUM(working_date) - NUM(RWP_START_DT).

MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS day_number_MOD.

https://community.qlik.com/t5/QlikView-App-Dev/A-method-to-repeate-the-Autonumber-or-RowNo-for-a-rolling-period/m-p/2130356#M1224742

Mod() is a modulo function that returns the non-negative remainder of an integer division. The first argument is the dividend, the second argument is the divisor, Both arguments must be integer values.

Syntax:

Mod(integer_number1, integer_number2)

Return data type: integer

Limitations:

integer_number2 must be greater than 0.

Examples and results:

Mod( 7,2 ): returns 1

Mod( 7.5,2 ): returns NULL

Mod( 9,3 ): returns 0

Mod( -4,3 ): returns 2

Mod( 4,-3 ): returns NULL

Mod( -4,-3 ): returns NULL