Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
mahamedfaijan
Contributor II

Backend script issue

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

Labels (1)
9 Replies
amrinder
Contributor

Re: Backend script issue

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 

x,

y,

z,

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

'Date' as Flag

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

Load 

x,

y,

z,

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

'Prem' as Flag

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

 

Load 

x,

y,

z,

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

mahamedfaijan
Contributor II

Re: Backend script issue

Hi Amrinder
I am loading this data from QVD.

it is like "from wxz.qvd"
mahamedfaijan
Contributor II

Re: Backend script issue

Any solution guys
chrwolf64
Contributor III

Re: Backend script issue

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];

mahamedfaijan
Contributor II

Re: Backend script issue

Awesome....!!!

Thank you so much.

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

mahamedfaijan
Contributor II

Re: Backend script issue

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

 

 

chrwolf64
Contributor III

Re: Backend script issue

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

mahamedfaijan
Contributor II

Re: Backend script issue

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

mahamedfaijan
Contributor II

Re: Backend script issue

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