Backend script issue

[Mahamed_Qlik]

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

[amrinder]

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]

Hi Amrinder
I am loading this data from QVD.

it is like "from wxz.qvd"

[Mahamed_Qlik]

Any solution guys

[cwolf]

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]

Awesome....!!!

Thank you so much.

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

[Mahamed_Qlik]

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]

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]

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]

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