topic Re: Backend script issue in QlikView

Backend script issue

Mahamed_Qlik — Wed, 25 Nov 2020 16:16:04 GMT

I am facing high reload time issue using wildmatch function in backend script in below :

if(wildmatch(Attribute, '*DATE*') or wildmatch(Attribute, '*date*') or wildmatch(Attribute, '*Date*'),
date(floor([Attribute Value]),'MM/DD/YYYY'),
if(wildmatch(Attribute, '*prem*') or wildmatch(Attribute, '*PREM*') or wildmatch(Attribute, '*Prem*'),
text(num([Attribute Value],'$#,##0.00')),
text([Attribute Value]))) as [Attribute Value],

It is taking 1hr to reload and if i skip this wildmatch then it taked hardly 10 mins.

Can you please how I can fix this issue and what I can user instead of wildmatch in script

I am not sure if it is issue with wildmatch or if statement.

Please help

Re: Backend script issue

amrinder — Wed, 03 Apr 2019 06:49:00 GMT

Hi,

Are you directly reading this data from DB table? If you are using QVD i would suggest first load optimized QVD then by taking resident of it you can use where clause on it.

Another thing you can do is

Load

date(floor([Attribute Value]),'MM/DD/YYYY') as AttributeValue,

'Date' as Flag

from ABC where wildmatch(Attribute,'DATE*','*date*');

Load

text(num([Attribute Value],'$#,##0.00')) as AttributeValue,

'Prem' as Flag

from ABC where wildmatch(Attribute,'*prem*','*PREM*','*Prem*');

Load

text(num([Attribute Value],'$#,##0.00')) as AttributeValue,

'OT' as Flag

from ABC where NOT wildmatch(Attribute,'*prem*','*PREM*','*Prem*',*DATE*,'*date*','*Date*');

Hope it helps.

-amrinder

Re: Backend script issue

Mahamed_Qlik — Wed, 03 Apr 2019 07:10:52 GMT

Hi Amrinder
I am loading this data from QVD.

it is like "from wxz.qvd"

Re: Backend script issue

Mahamed_Qlik — Mon, 08 Apr 2019 14:27:11 GMT

Any solution guys

Re: Backend script issue

cwolf — Tue, 09 Apr 2019 16:28:39 GMT

Work only on distinct subsets of your table.
Use Index() instead of Wildmatch().
If possible use Pick() instead of If().

FullTable:
Load * from wxz.qvd(qvd);

AttributeValues:
LOAD Distinct
Attribute,
[Attribute Value]
Resident FullTable;

Attributes:
LOAD Distinct
Attribute
Resident AttributeValues;

Left Join(Attributes)
LOAD
Attribute,
Upper(Attribute) as UAttribute
Resident Attributes;

Left Join(AttributeValues)
LOAD
Attribute,
if(Index(UAttribute,'DATE')>0,1,if(Index(UAttribute,'PREM')>0,2,3)) as AttributeType
Resident Attributes;

DROP Table Attributes;

Left Join(FullTable)
LOAD
Attribute,
[Attribute Value],
Pick(AttributeType,Date(Floor([Attribute Value])),Text(Num([Attribute Value],'$#,##0.00')),Text([Attribute Value])) as NewAttributeValue
Resident AttributeValues;

DROP Field [Attribute Value];
DROP Table AttributeValues;

RENAME Field NewAttributeValue to [Attribute Value];

Re: Backend script issue

Mahamed_Qlik — Wed, 10 Apr 2019 10:50:25 GMT

Awesome....!!!

Thank you so much.

It reduced reload time from 40mins to 12 mins. 🙂

Re: Backend script issue

Mahamed_Qlik — Wed, 10 Apr 2019 12:47:42 GMT

Hi Expert,

I found one thing , after executing below line at 00:06:40 ( Capture1.png) it seem halt for about 10 min then directly show the time 00:16:25( Capture2.png)

Please find attachement

Re: Backend script issue

cwolf — Wed, 10 Apr 2019 14:33:38 GMT

That means, that the last join takes round about 10min. That is normal for joining 26M rows against 400M rows over 2 fields.

Re: Backend script issue

Mahamed_Qlik — Thu, 11 Apr 2019 08:54:33 GMT

Okay.

Also I have one more doubt,

AttributeValues:
LOAD Distinct
Attribute,
[Attribute Value]
Resident FullTable;

Attributes:
LOAD Distinct
Attribute
Resident AttributeValues;

If we are already loading distinct records in first table "AttributeValues" then whey we are again taking its distinct in the second table "Attributes" which is resident of first itsself

Re: Backend script issue

Mahamed_Qlik — Thu, 11 Apr 2019 09:12:51 GMT

Also how I can rewrite below script to reduce reload time:
if(len(num([Attribute Value],'###,###,###,###,###'))>0,[Attribute],null()) as [Attribute Num],