topic Re: Index, Left/Right in QlikView

Index, Left/Right

Thu, 24 Jul 2014 10:09:30 GMT

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

datanibbler — Thu, 24 Jul 2014 10:13:07 GMT

Hi,

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

Re: Index, Left/Right

juleshartley — Thu, 24 Jul 2014 10:15:15 GMT

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

Thu, 24 Jul 2014 11:59:15 GMT

I don't have any Splitfield() function

Re: Index, Left/Right

juleshartley — Thu, 24 Jul 2014 12:27:37 GMT

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

Re: Index, Left/Right

Thu, 24 Jul 2014 12:55:32 GMT

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

Thu, 24 Jul 2014 13:49:49 GMT

Thanks a lot, works perfectly fine

Re: Index, Left/Right

MarcoWedel — Thu, 24 Jul 2014 14:14:18 GMT

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

Re: Index, Left/Right

Thu, 24 Jul 2014 14:22:29 GMT

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