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

[rajeshqvd]

If you load only second id it will match my Req if you load all ids it will miss increment order.

 

[rajeshqvd]

Hi Sameer your logic is matching My req but only issue is missing 3 veger creating increment number based on ID we should aggregate both ID and SubID.

[Sameer9585]

Hey @rajeshqvd , Try this one

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

data:
Load
*,
If(Name= 'Orange',Autonumber( SubID, 'Orange'), null()) as [Orange Cycle],
If(Name= 'Apple',Autonumber( SubID, 'Apple'), null()) as [Apple Cycle]
Resident Sheet3;

Drop Table Sheet3;

[Sameer9585]

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

data:
Load
*,
If(Name= 'Orange',Autonumber(ID & SubID, 'Orange'), null()) as [Orange Cycle],
If(Name= 'Apple',Autonumber(ID & SubID, 'Apple'), null()) as [Apple Cycle]
Resident Sheet3;

Drop Table Sheet3;

[rajeshqvd]

First logic is correct sameer this is wrong

[Sameer9585]

Hey @rajeshqvd  may be this one

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

data:
Load
*,
If(Name= 'Orange',Autonumber(ID & SubID, 'Orange'), null()) as [Orange Cycle],
If(Name= 'Apple',Autonumber(ID & SubID, 'Apple'), null()) as [Apple Cycle]
Resident Sheet3;

Drop Table Sheet3;

[rajeshqvd]

Hi Sameer,

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;

Above script matching my Req but 3 is missing in second ID Orange.

[Sameer9585 · Accepted solution]

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;

[rajeshqvd]

Thanks for your response. see below Required field is my desired output.

ID	SubID	Name	Orange_Cycle	Required for Orange	Apple_Cycle	Required for Apple	Banana_Cycle	Required for Banana
456123	111	Apple	-		1	1	-
456123	112	Orange	1	1	-		-
456123	113	banana	-		-		1	1
456123	114	Apple	-		2	2	-
456123	115	banana	-		-		2	2
456123	116	Orange	2	2	-		-
456123	118	Apple	-		3	3	-
456123	119	Orange	3	3	-		-
789456	111	Apple	-		4	1	-
789456	112	Orange	4	1	-		-
789456	113	banana	-		-		3	1
789456	114	Apple	-		5	2	-
789456	116	Orange	5	2	-		-
789456	1145	Orange	7	3	-		-
789456	1147	Orange	6	4	-		-
789456	11123	banana	-		-		4	2

[rajeshqvd]

Super!!!!!   

Tanks sameer this is what i want  😀!