Remove character from a loading table

[#1]

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

[MK_QSL · Accepted solution]

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

];

[sunny_talwar]

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.

[#4]

Thanks a lot! This works perfectly!!