Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mahamedfaijan
Creator II
Creator 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
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

mahamedfaijan
Creator II
Creator II
Author

Hi Amrinder
I am loading this data from QVD.

it is like "from wxz.qvd"
mahamedfaijan
Creator II
Creator II
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];

mahamedfaijan
Creator II
Creator II
Author

Awesome....!!!

Thank you so much.

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

mahamedfaijan
Creator II
Creator II
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.

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

mahamedfaijan
Creator II
Creator II
Author

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