How get increment number

[rajeshqvd]

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

Thanks in Advance

[Sameer9585]

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.

[Vegar]

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

[Sameer9585]

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]

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

[Sameer9585]

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]

Hi Sameer thanks for your quick reply 

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

[Vegar]

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.

[rajeshqvd]

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

[Vegar]

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....;