Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

'Substitute' question

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!

1 Solution

Accepted Solutions
sunny_talwar

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

];


Capture.PNG

View solution in original post

7 Replies
sunny_talwar

May be this:

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

UPDATE:

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

sunny_talwar

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

];


Capture.PNG

swuehl
MVP
MVP

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-3552183552000
18-3552-1183552001
18-3552-12183552012
18-3552-123183552123
Not applicable
Author

Thank you very much! Works like a charm.

swuehl
MVP
MVP

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.

Not applicable
Author

‌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