topic Load only part of a string in QlikView

Load only part of a string

robert_gansel — Mon, 11 Jan 2016 12:25:34 GMT

Hi Community,

I need help with loading just part of a string.

e.g. I have a string in the database which is "001.02" or "001-02".

When loading this into qlikview i just want to get "001" as a result - so only the left part before "." or "-".

I am sure this is very simple but I do not know which expression is needed.

Thanks in advance for help!

Best regards

Robert

sunny_talwar — Mon, 11 Jan 2016 12:31:43 GMT

May be this:

If(KeepChar(FieldName, '.-') = '.', SubField(FieldName, '.', 1), SubField(FieldName, '-', 1))

Kushal_Chawda — Mon, 11 Jan 2016 12:33:14 GMT

Is there only two separator "." and "_" or there is any other separator?

miguelbraga — Mon, 11 Jan 2016 12:34:33 GMT

Hi Robert,

Use SubField like this:

=SubField('001-02', '-', 1)

Put instead of '-' with '.' if you have '001.02'.

Sunny T response works like a charm also

Regards,

Kushal_Chawda — Mon, 11 Jan 2016 12:40:06 GMT

if there is only two separator then try like this

subfield(replace(replace(String,'.','*'),'-','*'),'*',1) as String_New

LOAD *, subfield(replace(replace(String,'.','*'),'-','*'),'*',1) as String_New Inline [

String

001.002

002_003

003.004];

Peter_Cammaert — Mon, 11 Jan 2016 13:49:21 GMT

or even simpler:

=Subfield(Replace(String, '.', '-'), '-', 1)

or in script:

...Subfield(Replace(String, '.', '-'), '-', 1) AS String_New, ...