topic Wildmatch and ** returning 0 in QlikView

Wildmatch and ** returning 0

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

Hi All,

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

Re: Wildmatch and ** returning 0

sunny_talwar — Tue, 02 Apr 2019 12:30:45 GMT

May be try to simplify it to this

Pick(WildMatch(Upper(Attribute), '*DATE*', '*PREM*') + 1,
  Text([Attribute Value]),
  Date(Floor([Attribute Value]), 'MM/DD/YYYY'),
  Text(Num([Attribute Value], '$#,##0.00'))
) as [Attribute Value],

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Tue, 02 Apr 2019 12:38:38 GMT

Thanks for sharing , I will try and let you know.
But I still have one query that you are also using wild-match function then how it will help to reduce reload time

Re: Wildmatch and ** returning 0

sunny_talwar — Tue, 02 Apr 2019 12:42:17 GMT

I am not sure if the WildMatch is the problem or if is the problem.... I have suggested to remove the if and test.

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Tue, 02 Apr 2019 12:44:05 GMT

Okay. I will user it and watch the result and also let you know.
Thanks.

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Tue, 02 Apr 2019 13:12:35 GMT

It is still taking of time to reload.

Re: Wildmatch and ** returning 0

sunny_talwar — Tue, 02 Apr 2019 13:26:45 GMT

Is this happening while loading from a qvd or any other source?

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Tue, 02 Apr 2019 13:32:28 GMT

loading from QVD only

Re: Wildmatch and ** returning 0

sunny_talwar — Tue, 02 Apr 2019 13:49:24 GMT

So, that is the issue.... create this new field before you create the qvd and then bring in this as a field and that should resolve your issue... The problem is that by creating this, you are breaking the optimized load. And this has nothing to do with this complicated calculation, just add 0 as Dummy and the optimized load will break

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Wed, 03 Apr 2019 06:25:50 GMT

I am sorry but I not getting you.
I have already created qvd and we loading this QVD in data model level and in there we are doing this trasformation to the field

Re: Wildmatch and ** returning 0

sunny_talwar — Wed, 03 Apr 2019 11:01:03 GMT

So, what I am trying to say is that create this new field before you create a qvd.... for example

Table:
LOAD FieldA,
FieldB,
....,
Pick(WildMatch(Upper(Attribute), '*DATE*', '*PREM*') + 1,
  Text([Attribute Value]),
  Date(Floor([Attribute Value]), 'MM/DD/YYYY'),
  Text(Num([Attribute Value], '$#,##0.00'))
) as [Attribute Value]
FROM XYZ;

STORE Table into XYZ.qvd (qvd);

and now load from your qvd with Attribute Value already fixed in it....

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Thu, 04 Apr 2019 06:16:26 GMT

Table:
LOAD FieldA,
FieldB,
....,
Pick(WildMatch(Upper(Attribute), '*DATE*', '*PREM*') + 1,
Text([Attribute Value]),
Date(Floor([Attribute Value]), 'MM/DD/YYYY'),
Text(Num([Attribute Value], '$#,##0.00'))
) as [Attribute Value]
FROM XYZ.qvd;

This is my script.

Re: Wildmatch and ** returning 0

sunny_talwar — Thu, 04 Apr 2019 11:05:28 GMT

Did you try changing it to what I mentioned 🙂

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Thu, 04 Apr 2019 12:30:25 GMT

Yes...It is still taking the same time. 😞

Re: Wildmatch and ** returning 0

sunny_talwar — Thu, 04 Apr 2019 12:38:16 GMT

What is the new script? Can you share both the scripts you use to create the qvd and then the script you use to load the qvd?

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Thu, 04 Apr 2019 13:17:43 GMT

see below is the script.

The comments line is creating issue. Just below it I put yours but that is again giving me same result

LOAD
Key,
factkey,
Attribute,
// 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],
Pick(WildMatch(Upper(Attribute), '*DATE*', '*PREM*') + 1,
Text([Attribute Value]),
Date(Floor([Attribute Value]), 'MM/DD/YYYY'),
Text(Num([Attribute Value], '$#,##0.00'))
) as [Attribute Value],
if(len(num([Attribute Value],'###,###,###,###,###'))>0,[Attribute],null()) as [Attribute Num],
num([Attribute Value],'###,###,###,###,###') as [Attribute Value Num]
FROM
$(vQVDPath)Fact.qvd(qvd);

Re: Wildmatch and ** returning 0

sunny_talwar — Thu, 04 Apr 2019 13:39:13 GMT

Are you having difficulty understand my suggestion? I don't even think you are even trying to do what I have proposed.

Re: Wildmatch and ** returning 0

Mahamed_Qlik — Thu, 04 Apr 2019 14:10:26 GMT

Yes I got it, you mean to say that I should create this new field at the time of QVD store.
But, I am storing this QVD at ETL so dont you think it will good practice to do such transformation in ETL script ?