Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
sunny_talwar
Not applicable

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
sunny_talwar
Not applicable

Re: 'Substitute' question

May be this:

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

UPDATE:

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

sunny_talwar
Not applicable

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

Highlighted
swuehl
Not applicable

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.

swuehl
Not applicable

Re: 'Substitute' question

Have you noticed the little difference in table results ?

sunny_talwar
Not applicable

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