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 

Highlighted
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

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