Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

8 Replies
datanibbler
Not applicable

Re: Index, Left/Right

Hi,

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

juleshartley
Not applicable

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

Not applicable

Re: Index, Left/Right

I don't have any Splitfield() function

juleshartley
Not applicable

Re: Index, Left/Right

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

Not applicable

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

Not applicable

Re: Index, Left/Right

Thanks a lot, works perfectly fine

MarcoWedel
Not applicable

Re: Index, Left/Right

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:

QlikCommunity_Thread_126922_Pic1.png

regards

Marco

Not applicable

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