Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
you can use the SubField() function to split a string using a specific character and then get the part you need:
If all your RENTALSTOCKID contain a / afrer the resource ID this formula
SubField(RENTALSTOCKID, '/', 1)
will give you the resource id.
Regards
Fabiano
you can use the SubField() function to split a string using a specific character and then get the part you need:
If all your RENTALSTOCKID contain a / afrer the resource ID this formula
SubField(RENTALSTOCKID, '/', 1)
will give you the resource id.
Regards
Fabiano
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?
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.
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);
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]
following the instruction of @marcus_sommer I created this script:
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
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
both expressions return a text value, so you should be able to use both in the applymap function.
Regards
Fabiano
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.