Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 [
s
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
];
Should POARR-VILLA return VILLA or null? In the original requirement the value should have been null.
yes you're right
POARR-VILLA --> null
but why?
0072-Villa-B9-M --> B9-M
and
POARR-VILLA --> null
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.
maybe this
load
s,
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 [
s
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
];