Alternative group continues numbering

[fanian]

Hi! We have some BOM functionality and need to create a new column with end-to-end numbering groups of all Alternative components.
The components are alternative when they presents in one BOM and one Item Group, but if they presents in another BOM and another Item Group and at least one of them the same as in other group - all Materials in this group are alternatives too.

Initial table:

BOM	Material	Item Group
A-Bom	Cabel 1	01
A-Bom	Cabel 2	01
A-Bom	HDD 1	02
A-Bom	HDD 2	02
B-Bom	Cabel 2	04
B-Bom	Cabel 3	04
C-Bom	MB 1	01
C-Bom	HDD 1	05
C-Bom	HDD 2	05
D-Bom	MB 1	01
D-Bom	MB 3	01
D-Bom	HDD 2	02

Waiting results:

BOM	Material	Item Group	Alt Group End-to-End
A-Bom	Cabel 1	01	1
A-Bom	Cabel 2	01	1
A-Bom	HDD 1	02	2
A-Bom	HDD 2	02	2
B-Bom	Cabel 2	04	1
B-Bom	Cabel 3	04	1
C-Bom	MB 1	01	3
C-Bom	HDD 1	05	2
C-Bom	HDD 2	05	2
D-Bom	MB 1	01	3
D-Bom	MB 3	01	3
D-Bom	HDD 2	02	2

Please give me advice. Can we do it with Qlik and how?

[Saravanan_Desingh]

Try this,

tab1:
LOAD RowNo() As RowID,*,SubField(Material,' ',1) As _Material INLINE [
    BOM, Material, Item Group
    A-Bom, Cabel 1, 01
    A-Bom, Cabel 2, 01
    A-Bom, HDD 1, 02
    A-Bom, HDD 2, 02
    B-Bom, Cabel 2, 04
    B-Bom, Cabel 3, 04
    C-Bom, MB 1, 01
    C-Bom, HDD 1, 05
    C-Bom, HDD 2, 05
    D-Bom, MB 1, 01
    D-Bom, MB 3, 01
    D-Bom, HDD 2, 02
];

Left Join(tab1)
LOAD DISTINCT _Material, AutoNumber(_Material) As [Alt Group End-to-End]
Resident tab1
Order By RowID;

Drop Field _Material;

[fanian]

Thanks, but you didn't understand me. Hdd 1, Hdd 2: These names are only for example. We have different names: Hdd 1, Hdd32, anotherDisk, harddrive43, etc. So we can't substring and then compare. We have to compare everithing.