Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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!

Tags (1)
1 Solution

Accepted Solutions

Re: 'Substitute' question

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

7 Replies

Re: 'Substitute' question

May be this:

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

UPDATE:

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

Re: 'Substitute' question

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

MVP
MVP

Re: 'Substitute' question

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

Re: 'Substitute' question

Thank you very much! Works like a charm.

Highlighted
MVP
MVP

Re: 'Substitute' question

Have you noticed the little difference in table results ?

Re: 'Substitute' question

I just noticed the difference as well.

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

Not applicable

Re: 'Substitute' question

‌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

Community Browser