# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Contributor II

## How get increment number

Hello,

have to create two set of increment numbers in Qlik

Data

 ID SubID Name 456123 111 Apple 456123 112 Orange 456123 113 456123 114 Apple 456123 114 Apple 456123 114 Apple 456123 115 456123 116 Orange 456123 116 Orange 456123 116 Orange 456123 116 Orange 456123 118 Apple 456123 119 Orange 789456 111 Apple 789456 112 Orange 789456 113 789456 114 Apple 789456 114 Apple 789456 114 Apple 789456 11123 789456 116 Orange 789456 116 Orange 789456 116 Orange 789456 116 Orange 789456 1147 Orange 789456 1145 Orange

Required Output is

 Apple Cycle Orange Cycle

Like below:

 ID SubID Name Apple Cycle Orange Cycle 456123 111 Apple 1 456123 112 Orange 1 456123 113 456123 114 Apple 2 456123 114 Apple 2 456123 114 Apple 2 456123 115 456123 116 Orange 2 456123 116 Orange 2 456123 116 Orange 2 456123 116 Orange 2 456123 118 Apple 3 456123 119 Orange 3 789456 111 Apple 1 789456 112 Orange 1 789456 113 789456 114 Apple 2 789456 114 Apple 2 789456 114 Apple 2 789456 11123 789456 116 Orange 2 789456 116 Orange 2 789456 116 Orange 2 789456 116 Orange 2 789456 1147 Orange 3 789456 1145 Orange 4

Labels (3)

• ### Script Logic

1 Solution

Accepted Solutions
Contributor II

## Re: How get increment number

Hey @rajeshqvd ,

Try this works perfectly

ID,
SubID,
Name,
AutoNumber(SubID,ID&Name) as key
FROM [lib://Desktop/sfdgsdfg.xlsx]
(ooxml, embedded labels, table is Sheet3);

data:
*,
if(Name='Apple',key) as Apple_Cycle,
if(Name='Orange',key) as Orange_Cycle
Resident Sheet3;

Drop Table Sheet3;

19 Replies
Contributor II

## Re: How get increment number

I have a doubt that in the last two rows

 789456 1147 Orange 3 789456 1145 Orange 4

I feel the output shout be like this

 789456 1147 Orange 4 789456 1145 Orange 5

once check and tell me.

Partner

## Re: How get increment number

Hi, try the load  script snippet below.

ID,

SubID,

Name,

If(Name= 'Orange',Autonumber(ID & '|' & SubID, 'orange'), null()) as [Orange Cycle],

If(Name='Apple', Autonumber(ID & '|' & SubID, 'apple'), null()) as [Apple Cycle]

From  ..

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Contributor II

## Re: How get increment number

Hi @Vegar ,

That's what If we use autonumber then we get the last two rows displayed as I mentioned above so It doesn't gives the correct output. And we use it we have one more problem that for 456123,114,Apple and it should be displayed 3 times but it will display only once so please the result and update the code as required.

With Regards,

Sameer

Contributor II

## Re: How get increment number

no sameer its 3 4 because it consider ID and Sub ID

Contributor II

## Re: How get increment number

Hi @rajeshqvd ,

If the above mentioned are not your priorities then you can use

ID,
SubID,
Name,
AutoNumber(SubID,Name) as key
FROM [lib://Desktop/sfdgsdfg.xlsx]
(ooxml, embedded labels, table is Sheet3);

data:
*,
if(Name='Apple',key) as Apple_Cycle,
if(Name='Orange',key) as Orange_Cycle
Resident Sheet3;

Drop Table Sheet3;

Contributor II

## Re: How get increment number

Actually requirement is so many names this logic implemented in original data so many numbers missing like above 3.

Partner

## Re: How get increment number

Yes you are correct regarding the last lines. I don't see the logic why they should look like that so without an explanation from @rajeshqvd I will assume they're a typo.

I don't think you are correct regarding 456123,114,Apple , all Apple rows should get a value from this expression.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Contributor II

## Re: How get increment number

we need correct increment order for all Flags {like Apple and Orange}.

Partner

## Re: How get increment number

Oh, I noticed a logical glitch in my first post

The expression should probably be

ID,

SubID,

Name,

If(Name= 'Orange',Autonumber( SubID, 'orange'), null()) as [Orange Cycle],

If(Name= 'Apple',Autonumber( SubID, 'apple'), null()) as [Apple Cycle]

From....;

Plees ekskuse my Swenglish and or Norweglish spelling misstakes