topic Re: 'Substitute' question in QlikView

'Substitute' question

Thu, 07 Apr 2016 19:46:14 GMT

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!

Re: 'Substitute' question

sunny_talwar — Thu, 07 Apr 2016 20:47:47 GMT

May be this:

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

UPDATE:

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

Re: 'Substitute' question

sunny_talwar — Thu, 07 Apr 2016 20:51:37 GMT

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

];

Re: 'Substitute' question

swuehl — Thu, 07 Apr 2016 21:00:34 GMT

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

Re: 'Substitute' question

Thu, 07 Apr 2016 22:34:53 GMT

Thank you very much! Works like a charm.

Re: 'Substitute' question

swuehl — Thu, 07 Apr 2016 22:44:18 GMT

Have you noticed the little difference in table results ?

Re: 'Substitute' question

sunny_talwar — Thu, 07 Apr 2016 22:49:24 GMT

I just noticed the difference as well.

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

Re: 'Substitute' question

Thu, 07 Apr 2016 22:53:02 GMT

‌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