Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I'm trying to load a table that is made in this way:
Location code | Product code |
---|---|
1 | 1234567 |
2 | 7654320 |
3 | 11223344 |
4 | 149486731 |
5 | 1241148720 |
6 | 12498562341 |
My data are different, but it's just to give you an idea and I've got 400k records in my table. The Product code field can have 7-8-9-10 characters and I would like to remove the last character when I load the table only if it is a '0' and only if it has a Location code equal to 1 or equal to 2 or equal to 3. Product codes can also have 0 in the middle, but I'd like to remove them only if they are at the end of the code.
So if I have got a Product Code like 1234567890 and it's location code is 1 or it's 2 or it's 3 I would like to load it as 123456789.
Thanks a lot for your help and for your attention!!!
Best regards
Nicolò Cogno
Messaggio modificato da Nicolò Cogno
Data:
Load
[Location code],
If(Match([Location code],1,2,3) and Right([Product code],1) = '0', Left([Product code],Len([Product code])-1),[Product code]) as [Product code]
Inline
[
Location code, Product code
1, 12345670
2, 7654320
3, 112233440
4, 149486731
5, 1241148720
6, 12498562341
3,1234567890
];
Data:
Load
[Location code],
If(Match([Location code],1,2,3) and Right([Product code],1) = '0', Left([Product code],Len([Product code])-1),[Product code]) as [Product code]
Inline
[
Location code, Product code
1, 12345670
2, 7654320
3, 112233440
4, 149486731
5, 1241148720
6, 12498562341
3,1234567890
];
Or even this
If(Match([Location code],1,2,3) and Mod([Product code], 10) = '0', Mod([Product code], 10), [Product code]))
This assuming the product code is always a number and not text.
Thanks a lot! This works perfectly!!