'Substitute' question

[#1]

Hi!

My first post/question.

I'm loading data from multiple sources, and the ProductId is formatted in different ways.

In most sources its formatted like below (goal😞

183552000

183552001

183552012

183552123

But in some sources it looks like below (needs to be formatted😞

18-3552

18-3552-1

18-3552-12

18-3552-123

So first of all I need to get rid of the " - ", but that I can solve with substitute. But what I'm struggling with is how I deal with adding the zeros before the last number(s), making them all the same length.

Any ideas on how to solve this would be greatly appreciated!

[sunny_talwar]

May be this:

Num(PurgeChar(ProductId, '-'), '000000000')

UPDATE:

PurgeChar(ProductId, '-') * Pow(10, (9 - Len(PurgeChar(ProductId, '-'))))

[sunny_talwar · Accepted solution]

Test script:

Table:

LOAD *,

  PurgeChar(ProductId, '-') * Pow(10, (9 - Len(PurgeChar(ProductId, '-')))) as Test;

LOAD * Inline [

ProductId

18-3552

18-3552-1

18-3552-12

18-3552-123

];

[swuehl]

Maybe like

Table:

LOAD *,

  Subfield(ProductId,'-',1)&Subfield(ProductId,'-',2)&num(Subfield(ProductId&'-0','-',3),'000') as Test;

LOAD * Inline [

ProductId

18-3552

18-3552-1

18-3552-12

18-3552-123

];

ProductId	Test
18-3552	183552000
18-3552-1	183552001
18-3552-12	183552012
18-3552-123	183552123

[#5]

Thank you very much! Works like a charm.

[swuehl]

Have you noticed the little difference in table results ?

[sunny_talwar]

I just noticed the difference as well.

Zeros at the end vs. Zeros at after the second hyphen.

[#8]

‌I hadn't noticed - was thinking it will be tomorrows project to learn how both of your solutions / functions actually work and how they differ