Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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.