Need help with the function len and left

[Rajiv-Dharia]

Hi All,

Need help with below script,

LOAD
     text(left(RENTALSTOCKID,4)) as [Resource No],
     RENTALSTOCKID
   
FROM
[$(vFetchpath)RSTRENTALSTOCK.qvd]
(qvd) Where len(RENTALSTOCKID)=6 and index(RENTALSTOCKID,'/')=5;

Now our the field RENTALSTOCKID is 5 digits also. can someone suggest the change in the script please.

I have attatched the screenshot of the application where the RENTALSTOCKID 30025 should not comes under 3002.

[Fabiano_Martino_Intelco · Accepted solution]

@Rajiv-Dharia 

you can use the SubField() function to split a string using a specific character and then get the part you need:

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions...

If all your RENTALSTOCKID contain a / afrer the resource ID this formula

SubField(RENTALSTOCKID, '/', 1)

will give you the resource id.

Regards

Fabiano

[Rajiv-Dharia]

Hi Fabiano,

I am using QlikView 11.2 version. Problem is this application takes 7-8 hours to complete the reload. I have not yet tried subfield function yet.

I tried the below script and waiting for the reload to complete.

LOAD
     text(left(RENTALSTOCKID,4))or text(left(RENTALSTOCKID,5)) as [Resource No],
     RENTALSTOCKID
   
FROM
[$(vFetchpath)RSTRENTALSTOCK.qvd]
(qvd) WHERE (len(RENTALSTOCKID) >= 6 and index(RENTALSTOCKID, '/') >= 5);

Any suggestion?

[marcus_sommer]

Your second example isn't the same as the first one and isn't correct because of the OR connection:

 text(left(RENTALSTOCKID,4)) or text(left(RENTALSTOCKID,5)) as [Resource No]

Beside this a load of two fields with a string-function and also one in the where-clause must be extremely huge if it takes about 8 hours - probably billions of records ... at least within a sensible sized environment. I assume that's the size isn't such huge else that your storage/network is slow and/or your application is running out of RAM and using the virtual RAM from the disc.

Beyond that I suggest to extract the essential information in beforehand (the step which creates the qvd or even earlier) - means extracting the wanted [Resource No] as well as len() and number of '/' from the ID and a flag which combines these information. With it such qvd-load could be performed optimized.

Further if your datasets are larger you should consider to implement incremental approaches.

[Rajiv-Dharia]

By using OR or And its not showing any data.

LOAD
     text(left(RENTALSTOCKID,4))or text(left(RENTALSTOCKID,5)) as [Resource No],
     RENTALSTOCKID
   
FROM
[$(vFetchpath)RSTRENTALSTOCK.qvd]
(qvd) WHERE (len(RENTALSTOCKID) >= 6 and index(RENTALSTOCKID, '/') >= 5);

[marcus_sommer]

Like above hinted that is an invalide statement:

text(left(RENTALSTOCKID,4)) or text(left(RENTALSTOCKID,5)) as [Resource No]

You need to decide explicitly which string-part you want - which may follow this kind of logic:

if(MySubstring1 = 'Pattern1', MySubstring1,
if(MySubstring2 = 'Pattern2', MySubstring2, 'anything else')) as [Resource No] 

[Fabiano_Martino_Intelco]

Hi @Rajiv-Dharia 

following the instruction of @marcus_sommer I created this script:

Data:

Load * Inline [

RENTALSTOCKID

30001/1

30157/1

30170/1

30003/1

30003/2

1001/1

1002/1

1002/2

123456/1

123456/2

]

;

Elaboration:

Load

*,

    SubField(RENTALSTOCKID, '/', 1) as [Resource No]

Resident Data;

It will get the part before the "/" character no matter where it is.

Also, I suggest to avoid complex WHERE conditions in the Load statement from a .QVD. If you have lots of records it will slow down the procedure.

Regards

Fabiano

[Rajiv-Dharia]

Hi Fabriano,

Sorry for the late reply. I tried it in a test application and results are correct now.

I am just wondering if this function will work in the Applymap function also. Below is the script.

LOAD
        text(left(RENTALSTOCKID,4)) as [Resource No],
       applyMap('ResourceTypeMap',text(left(RENTALSTOCKID,4))) as [Resource Type],
       RENTALSTOCKID,    
        applymap('Docuref_MaintPL',text(RECID),0) as POINTS,
        1 as POINTSOFF,
       1 as MaintDoneFlag,
       text(RECID) as RECID,
     22              as TYPE,
     date(Floor(PLANNEDEXECUTIONDATETIME)) as DATEPBU,
     QTY,
     SARCALCULATEDQTYORIG,
     if(QTY<=SARCALCULATEDQTYORIG,1,0) as OnTime   
     
     
FROM
[$(vFetchpath)PRPMAINTENANCELOG.qvd]
(qvd) Where year(PLANNEDEXECUTIONDATETIME)>2013 and floor(PLANNEDEXECUTIONDATETIME)<=today();

If I replace text(left(RENTALSTOCKID,4) with subfield(RENTALSTOCKID,’/’1)

Thanks for your help

[Fabiano_Martino_Intelco]

Hi @Rajiv-Dharia 

both expressions return a text value, so you should be able to use both in the applymap function.

Regards

Fabiano

[marksouzacosta]

I'm intrigued about this 7-8 hours to reload. I have apps with more than 3 billion records that don't take more than 2 hours to reload. @Rajiv-Dharia I think we can help you to reduce your reload time.