Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rammuthiah
Contributor II

Cleansing the input data

I have input data like this. Need output like below

Input

Output

BlaBla-OM-17238812

OM-17238812

BlaBla-(@hgjhg)-DR-OM-17230000

OM-17230000

MID-BBM-OM-16210030

OM-16210030

(@hgjhg)OM-FR15AR0101

OM-FR15AR0101

BlaBlaBla-OM-DR-10000

OM-DR-10000

OM-GPC-1235-4 postn

OM-GPC-1235

5 Replies
MVP
MVP

Re: Cleansing the input data

This works, except for the last one

Table:

LOAD *,

'OM-' & Left(SubField(Input, 'OM-', -1), FindOneOf(SubField(Input, 'OM-', -1), '0123456789', -1)) as Calculated_Output;

LOAD * INLINE [

    Input, Output

    BlaBla-OM-17238812, OM-17238812

    BlaBla-(@hgjhg)-DR-OM-17230000, OM-17230000

    MID-BBM-OM-16210030, OM-16210030

    (@hgjhg)OM-FR15AR0101, OM-FR15AR0101

    BlaBlaBla-OM-DR-10000, OM-DR-10000

    OM-GPC-1235-4 postn, OM-GPC-1235

];

Capture.PNG

How do we know what is included in the end?

rammuthiah
Contributor II

Re: Cleansing the input data

Thank you Sunny, In every row after number we have some notes. I missed to add those except in last row.

MVP
MVP

Re: Cleansing the input data

What do you mean, can you update the sample data or provide new one?

rammuthiah
Contributor II

Re: Cleansing the input data

Every row will be like the last one that you highlighted

BlaBla-OM-17238812 hgjhg

OM-17238812

BlaBla-(@hgjhg)-DR-OM-17230000 hueytrgf

OM-17230000

MID-BBM-OM-16210030 egrefscjsh

OM-16210030

(@hgjhg)OM-FR15AR0101 wtyfef

OM-FR15AR0101

BlaBlaBla-OM-DR-10000 sakljhfe -jkdfhdef

OM-DR-10000

OM-GPC-1235-4 postn

OM-GPC-1235

antoniotiman
Honored Contributor III

Re: Cleansing the input data

Try like this

LOAD 'OM-'&TextBetween(Input&' ','OM-',' ') as Output 

Regards,

Antonio