Index, Left/Right

[#1]

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

[datanibbler]

Hi,

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

[juleshartley]

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

[#4]

I don't have any Splitfield() function

[juleshartley]

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

[#6 · Accepted solution]

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

[#7]

Thanks a lot, works perfectly fine

[MarcoWedel]

Hi Florian,

maybe also helpful:

LOAD Date#(SubFields, 'YYYYMMDD') as Date
Where Len(SubFields)=8 and IsNum(SubFields);
LOAD SubField(DateRaw, '_') as 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

[#9]

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