topic Re: Data Split in QlikView

Data Split

Mon, 03 Aug 2015 10:19:19 GMT

HI,

I have data something like the below

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

I need to display only the last four numbers that are before '-'.

Answer will be something like below.

0543

5171

0043

0005

Null

0009

Null

0011

Null

Please find the attachment.

Regards,

Keerthi KS

Re: Data Split

robert_mika — Mon, 03 Aug 2015 10:21:38 GMT

=KeepChar('TMD-VILLA-0005','0123456789')

Load

KeepChar( Your Field,'0123456789') as Order

Re: Data Split

maxgro — Mon, 03 Aug 2015 10:23:09 GMT

load

if(index(s, '-') > 1, SubField(s, '-', -1), null()) as t

inline [

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

];

Re: Data Split

Mon, 03 Aug 2015 10:24:26 GMT

Only the number before '-' is required.

Re: Data Split

robert_mika — Mon, 03 Aug 2015 10:26:45 GMT

Sorry my bad.

Massimo nailed it.

Re: Data Split

Mon, 03 Aug 2015 10:32:31 GMT

If i have data something like 0072-Villa-B9-M i need B9-M. Can you help me with this.

Sorry forgot to mention earlier.

Re: Data Split

ogster1974 — Mon, 03 Aug 2015 10:38:32 GMT

something like this will handle your extra requirement.

Check for the 5th char from the right if its a "-" display the 4 chars at the end of your string. I think this covers all your use cases.

Re: Data Split

robert_mika — Mon, 03 Aug 2015 10:38:34 GMT

Try:

=TextBetween('0072-Villa-B9-M','-','',2)

Re: Data Split

maxgro — Mon, 03 Aug 2015 10:43:46 GMT

do you mean

when there are 3 '-', get the last (right) 2 fields?

when 4 the last 3 fields?

......

if not we need some other rule to extract the correct string

Re: Data Split

Mon, 03 Aug 2015 10:50:18 GMT

Yes Massimo Exatly that is what is required

Re: Data Split

maxgro — Mon, 03 Aug 2015 11:14:37 GMT

try with

load

if(SubStringCount(s, '-')>=3, mid(s, index(s, '-', SubStringCount(s, '-')-1)+1),

if(SubStringCount(s, '-')>=1, SubField(s, '-', -1),

null())) as t

inline [

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

0072-Villa-B9-M

1-2-Villa-B9-M

1-2-3-Villa-B9-M

1-2-3-4-Villa-B9-M

];

Re: Data Split

ogster1974 — Mon, 03 Aug 2015 12:23:02 GMT

Should POARR-VILLA return VILLA or null? In the original requirement the value should have been null.

Re: Data Split

maxgro — Mon, 03 Aug 2015 12:52:58 GMT

yes you're right

POARR-VILLA --> null

but why?

0072-Villa-B9-M --> B9-M

and

POARR-VILLA --> null

Re: Data Split

ogster1974 — Mon, 03 Aug 2015 13:11:47 GMT

As a guess VILLA is just a type identifier rather than a record ID. There seems a mixture of data quality in the example records hence the cleaning requirement.

That's why I suggested as long as the output is always 4 char in length you can can check if its a valid 4 length char by seeing if the 5th char from the right is a "-". That would handle all the cases displayed to date including the extra one he came up with.

Re: Data Split

maxgro — Mon, 03 Aug 2015 13:37:07 GMT

maybe this

load

if(len(t)<>4, null(), t) as t;

load

if(SubStringCount(s, '-')>=3, mid(s, index(s, '-', SubStringCount(s, '-')-1)+1),

if(SubStringCount(s, '-')>=1, SubField(s, '-', -1),

null())) as t

inline [

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

0072-Villa-B9-M

1-2-Villa-B9-M

1-2-3-Villa-B9-M

1-2-3-4-Villa-B9-M

];