Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If you load only second id it will match my Req if you load all ids it will miss increment order.
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.
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;
First logic is correct sameer this is wrong
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;
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.
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;
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 |
Super!!!!!
Tanks sameer this is what i want 😀!