8 Replies Latest reply: Jul 24, 2014 10:22 AM by Joseph Simmons

# Index, Left/Right

Hi all,

I'm trying to do recover some dates in my script but I don't know What function would best fit with my problem : Left/right, Index?:

Ex :

If 20140302_011 then 20140302

If 39_204140302_011 then 20140302

If 20_2_20120515_001 then 20120515

If TEST_20120703_001 then 20120703

These are the only the 4 occurences.

I'd be greaful for any help.

Many thanks,

Florian

• ###### Re: Index, Left/Right

Hi,

if there is always the underscore as separator, you can use SPLITFIELD() instead.

• ###### Re: Index, Left/Right

subfield() then filter to fields where len()=8 (assuming this is reliable). Otherwise use Date#() to convert to date and check that is valid

• ###### Re: Index, Left/Right

I don't have any Splitfield() function

• ###### Re: Index, Left/Right

Sorry - got confused by the last answer. The function is subfield([Field], '_')

• ###### Re: Index, Left/Right

Hi Florian,

you could use subfield() or textbetween() for this, as each of your options are slightly different though, you would need to nest this to give the right output for each. Something like the below should give the right result for each scenario

=If(Len(Subfield(Date,'_',1))>4, Subfield(Date,'_',1)

,If(Len(Subfield(Date,'_',2))>4, Subfield(Date,'_',2)

,Subfield(Date,'_',3)

))

hope that helps

thanks

Joe

• ###### Re: Index, Left/Right

Thanks a lot, works perfectly fine

• ###### Re: Index, Left/Right

Hi Florian,

```LOAD Date#(SubFields, 'YYYYMMDD') as Date
Where Len(SubFields)=8 and IsNum(SubFields);
Inline [
DateRaw
20140302_011
39_20140302_011
20_2_20120515_001
TEST_20120703_001
TEST_20120704_001
TEST_2012070X_001
TEST_TEST_20130506_001_002
TEST_TEST_20130507_001_002
TEST_20130508_001_002
40_20130509_001_20130510_002
];
```

This solution generates Dates/Timestamps thus enabling date opertions:

regards

Marco

• ###### Re: Index, Left/Right

that's neat too I like it, just need to be careful if you have a large data set as you are inflating with the subfield before reducing so could be quite a resource hungry load

Joe