Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rajeshqvd
Contributor II

Re: How get increment number

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

Capture1.PNG 

rajeshqvd
Contributor II

Re: How get increment number

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

Re: How get increment number

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

Re: How get increment number

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

Re: How get increment number

First logic is correct sameer this is wrong

Sameer9585
Contributor II

Re: How get increment number

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

Re: How get increment number

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

Highlighted
rajeshqvd
Contributor II

Re: How get increment number

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

IDSubIDNameOrange_CycleRequired for OrangeApple_CycleRequired for AppleBanana_CycleRequired for Banana
456123111Apple- 11- 
456123112Orange11- - 
456123113banana- - 11
456123114Apple- 22- 
456123115banana- - 22
456123116Orange22- - 
456123118Apple- 33- 
456123119Orange33- - 
789456111Apple- 41- 
789456112Orange41- - 
789456113banana- - 31
789456114Apple- 52- 
789456116Orange52- - 
7894561145Orange73- - 
7894561147Orange64- - 
78945611123banana- - 42
rajeshqvd
Contributor II

Re: How get increment number

Super!!!!!   

Tanks sameer this is what i want  😀!