topic Re: How do I get this done skript in QlikView

How do I get this done skript

kkarlste — Mon, 10 Apr 2017 12:35:46 GMT

This is a data

This is correct model

letterletterletter-numbernumbernumber

For example BP-100 or AFP-296

This is data

100BP

100BR

102KAN

106HAO

109HAV

10CBA

10UAG

111ISS

AFO598

AFP296

AFT698

AFU291

AFX404

AGE590

AGM210

AGR941

AGT522

AGT555

AGT561

AGT563

AGT566

AGT923

AGT952

Re: How do I get this done skript

Miguel_Angel_Baeyens — Mon, 10 Apr 2017 12:43:58 GMT

Not clear which your requirement is, but perhaps this is close to what you want to achieve:

KeepChar(Field, 'abcdefghijklmnopqrstuvwxzyABCDEFGHIJKLMNOPQRSTUVWXYZ') & '-' & KeepChar(Field, '0123456789') AS CleanField

in the script

Re: How do I get this done skript

sunny_talwar — Mon, 10 Apr 2017 12:44:07 GMT

What are you looking to do?

Re: How do I get this done skript

kkarlste — Mon, 10 Apr 2017 12:57:07 GMT

This is good but same column is this 109HAV and this AFP296
This must be first letter and then number
For example

HAV-109

AFP-296

Re: How do I get this done skript

Miguel_Angel_Baeyens — Mon, 10 Apr 2017 13:02:31 GMT

That's what I meant by clarifying the requirement:

If(Len(KeepChar(Left(Field, 1), '0123456789')) = 0, // if the first character is not a number

KeepChar(Field, 'abcdefghijklmnopqrstuvwxzyABCDEFGHIJKLMNOPQRSTUVWXYZ') & '-' & KeepChar(Field, '0123456789'))

AS CleanField

Re: How do I get this done skript

jpar0511 — Mon, 10 Apr 2017 13:02:38 GMT

On my side script of mbaeyens‌ is working as follow:

demo:

load * inline [Field

100BP

100BR

102KAN

106HAO

109HAV

10CBA

10UAG

111ISS

AFO598

AFP296

AFT698

AFU291

AFX404

AGE590

AGM210

AGR941

AGT522

AGT555

AGT561

AGT563

AGT566

AGT923

AGT952]

;

neu:

load *,

KeepChar(Field, 'abcdefghijklmnopqrstuvwxzyABCDEFGHIJKLMNOPQRSTUVWXYZ') & '-' & KeepChar(Field, '0123456789') AS CleanField

resident demo;

drop table demo;

I got everything sorted as you wanted to have it. First letters followed by '-' and then the numbers...

Re: How do I get this done skript

kkarlste — Mon, 10 Apr 2017 13:12:56 GMT

Good but not work correct

Re: How do I get this done skript

Anil_Babu_Samineni — Mon, 10 Apr 2017 13:18:52 GMT

Seems works to me, Nothing changes on your script

Re: How do I get this done skript

Miguel_Angel_Baeyens — Mon, 10 Apr 2017 13:49:04 GMT

I just tested it in case I made any mistake and it is working, as several here have reported. If it doesn't for you either you are not applying the functions in the same order or your data source looks different than your original post, in which case, would be useful to have a sample on your actual data.

Re: How do I get this done skript

el_aprendiz111 — Mon, 10 Apr 2017 14:03:13 GMT

Hi,

Re: How do I get this done skript

Miguel_Angel_Baeyens — Mon, 10 Apr 2017 14:10:27 GMT

I don't think there could be a more literal "step-by-step"!

Just as a note, with INLINE you can also use fields as with any other table, and no need to use a preceding LOAD since you can do it in the same line.

Re: How do I get this done skript

kkarlste — Mon, 10 Apr 2017 17:16:37 GMT

Thanks that works good.

Br Kalle