Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Mahamed_Qlik
Specialist
Specialist

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
Creator
Creator

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

Mahamed_Qlik
Specialist
Specialist
Author

Hi Amrinder
I am loading this data from QVD.

it is like "from wxz.qvd"
Mahamed_Qlik
Specialist
Specialist
Author

Any solution guys
cwolf
Creator III
Creator III

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

Mahamed_Qlik
Specialist
Specialist
Author

Awesome....!!!

Thank you so much.

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

Mahamed_Qlik
Specialist
Specialist
Author

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

 

 

cwolf
Creator III
Creator III

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

Mahamed_Qlik
Specialist
Specialist
Author

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

Mahamed_Qlik
Specialist
Specialist
Author

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