Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
rajeshqvd
Contributor II

How get increment number

Hello,

have to create two set of increment numbers in Qlik

Data

IDSubIDName
456123111Apple
456123112Orange
456123113 
456123114Apple
456123114Apple
456123114Apple
456123115 
456123116Orange
456123116Orange
456123116Orange
456123116Orange
456123118Apple
456123119Orange
789456111Apple
789456112Orange
789456113 
789456114Apple
789456114Apple
789456114Apple
78945611123 
789456116Orange
789456116Orange
789456116Orange
789456116Orange
7894561147Orange
7894561145Orange

 

Required Output is 

Apple CycleOrange Cycle

 

Like below:

IDSubIDNameApple CycleOrange Cycle
456123111Apple1 
456123112Orange 1
456123113   
456123114Apple2 
456123114Apple2 
456123114Apple2 
456123115   
456123116Orange 2
456123116Orange 2
456123116Orange 2
456123116Orange 2
456123118Apple3 
456123119Orange 3
789456111Apple1 
789456112Orange 1
789456113   
789456114Apple2 
789456114Apple2 
789456114Apple2 
78945611123   
789456116Orange 2
789456116Orange 2
789456116Orange 2
789456116Orange 2
7894561147Orange 3
7894561145Orange 4

 

Thanks in Advance

Labels (3)
1 Solution

Accepted Solutions
Sameer9585
Contributor II

Re: How get increment number

Hey @rajeshqvd ,

Try this works perfectly

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

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

Drop Table Sheet3;

View solution in original post

19 Replies
Sameer9585
Contributor II

Re: How get increment number

I have a doubt that in the last two rows 

7894561147Orange 3
7894561145Orange 4

I feel the output shout be like this

7894561147Orange 4
7894561145Orange 5

once check and tell me.

Partner
Partner

Re: How get increment number

Hi, try the load  script snippet below.

 

 Load

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
Sameer9585
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

 

rajeshqvd
Contributor II

Re: How get increment number

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

Highlighted
Sameer9585
Contributor II

Re: How get increment number

Hi @rajeshqvd ,

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

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

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

Drop Table Sheet3;

rajeshqvd
Contributor II

Re: How get increment number

Hi Sameer thanks for your quick reply 

Capture.PNG

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

Partner
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
rajeshqvd
Contributor II

Re: How get increment number

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

Partner
Partner

Re: How get increment number

Oh, I noticed a logical glitch in my first post

The expression should probably be

Load

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