topic Re: Match function issue in QlikView

Match function issue

Anonymous — Wed, 27 Jan 2016 14:45:36 GMT

Hi All,

I am using Match function to retrieve a certain result which is working fine in the UI, but when I use the same in script, I am not getting the same result. I have used the following expression.

If(Match(DISMM,'VR','V1','ZA','ZD')=0,'Not_Stocked','Not_ClosestPlant')

DISMM is a field that contains the values,VR,V1,ZA,ZD,ND,PD,X0,Z1. When I used the expression in a list box under Expression. When I select Stocked or Not_ClosestPlant,the resultant DISMM values are getting selected, but with script, that is not happening.Can anyone please help.

Re: Match function issue

sunny_talwar — Wed, 27 Jan 2016 15:43:37 GMT

Would you be able to share a sample where it isn't working?

Re: Match function issue

sunny_talwar — Wed, 27 Jan 2016 15:48:23 GMT

Seems to be working here using this script:

Table:

LOAD *,

If(Match(DISMM,'VR','V1','ZA','ZD')=0,'Not_Stocked','Not_ClosestPlant') as New_Dim;

LOAD * Inline [

DISMM

];

Re: Match function issue

dominicmander — Wed, 27 Jan 2016 15:49:54 GMT

But the script is executing without failure and the field produced contains both Not_Stoked and Not_ClosestPlant?

If that is the case I suspect that wherever you're calculating this new field it is not correctly associated to DISMM in the data model. You could verify this in the table viewer.

Re: Match function issue

Anonymous — Wed, 27 Jan 2016 16:30:27 GMT

Hi Sunny,

Here is the complete script that I have been using. The bold part is where I am having the issue.

RCode:

Load %VBELNPOSNR,

if(PSTYV='TAB' OR PSTYV='TABN','0 – CrossDock',If(LIPS_WERKS=[WH Code 1],'1 – Cust_Geo_Prime',if(LIPS_WERKS='8041' or

LIPS_WERKS='8042'or LIPS_WERKS='8043'or LIPS_WERKS='8044' or LIPS_WERKS='8615','2 – Sci_Ed',

if(LIPS_WERKS='8031' or LIPS_WERKS='8032','3 – Furniture',

if(LIPS_WERKS='8033' or LIPS_WERKS='8035','4 – Export',

if(LIPS_WERKS='8036' or LIPS_WERKS='8037','5 – Seradigm',if(LIPS_WERKS <> [WH Code 1] AND LIPS_WERKS <> [WH Code 2] AND

LIPS_WERKS <> [WH Code 3] and LIPS_WERKS <> [WH Code 4] and LIPS_WERKS <> [WH Code 5] and

LIPS_WERKS <> [WH Code 6] and

LIPS_WERKS <> '8031' and LIPS_WERKS <> '8032' and LIPS_WERKS <> '8033' and LIPS_WERKS <> '8036' and LIPS_WERKS <> '8037'

and LIPS_WERKS <> '8041' and LIPS_WERKS <> '8042' and LIPS_WERKS <> '8043' and LIPS_WERKS <> '8044'

and LIPS_WERKS <> '8615' ,'6 – AWS_Override',if(%KNMTWERKS=LIPS_WERKS,'7 – Per_InfoRec',if(CLSTK_PLANT=LIPS_WERKS,'8 – Closest_StkPlnt',

if(Match(DISMM,'VR','V1','ZA','ZD')=0, '9-Not_Stocked',

'10 – Not_Closest_StkPlnt'

)))))))))) AS ReasonCode

Resident Delivery;

I have attached the selections screenshots of the Reason codes and their possible selections of DISMM when the above'IF' statement expression is used in UI and script. It shows that the script loaded expression is not selecting the relevant DISMM values.

Re: Match function issue

rwunderlich — Wed, 27 Jan 2016 17:08:07 GMT

From your screenshots, it looks to me like the script created "ReasonCode=Not_Stocked" is associating with the relevant DISMM values,

Is the question why the listbox created with <Expression> makes actual selections (green) in the DISMM field and the script field does not? The selection is a side effect of using <Expression>. Normal behavior of selecting one field is to associate, not select other fields. You can modify this behavior, see:

Scoping Selections with Aggr() | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Match function issue

Anonymous — Wed, 27 Jan 2016 17:30:21 GMT

Hi Rob,

I have attached the UI screenshot only to refer that the possibility of the selections is correct in the UI whereas in the script it is not giving the possible values in correct format which usually should happen by showing in White when loaded from script. My requirement to show to the selections even when loading through script is to get the correct count of keys associated to these selections. Only when the possible selections are made am I getting the correct count of the required keys which is not happening when loaded through Script.

Re: Match function issue

rwunderlich — Wed, 27 Jan 2016 18:32:08 GMT

Does your listbox expression contain the same large if() as the script? And they are returning different results?

-Rob

Re: Match function issue

swuehl — Wed, 27 Jan 2016 19:08:02 GMT

I think we would need to look at the data model and at the script sequence to understand if the two large if() statements are really equivalent.

Re: Match function issue

Anonymous — Thu, 28 Jan 2016 08:40:31 GMT

Yes

Re: Match function issue

Anonymous — Thu, 28 Jan 2016 08:45:42 GMT

PFA Datamodel and Expression in List Box

Re: Match function issue

dominicmander — Thu, 28 Jan 2016 10:32:59 GMT

It might be worth checking the uniqueness of your key %VBELNPOSNR with respect to DISMM.

If one %VBELNPOSNR has multiple records with different DISMM then you will end up with multiple entries in RCode for the same %VBELNPOSNR but with both '9-Not_Stocked' and '10 - Not_Closest_StkPlnt' and so filtering on either will not exclude all the records you expect.

Could this be the case?

Re: Match function issue

Anonymous — Thu, 28 Jan 2016 16:11:25 GMT

Yes Dominic, That is the case, but isn't there a way to show only those needed as possible selections when either '9-Not_Stocked' or '10 - Not_Closest_StkPlnt' is selected and filter out the unwanted.

Re: Match function issue

dominicmander — Thu, 28 Jan 2016 16:17:55 GMT

Yes, If there are %VBELNPOSNRs that have only one ReasonCode then I would expect those to be filtered out when you select the other ReasonCode.

If you create a straight table with RCode.ReasonCode as the dimension and count(DISTINCT [%VBELNPOSNR]) as the measure and clear all filters then does it show different counts against '9-...' and '10-...'?

Re: Match function issue

Anonymous — Fri, 29 Jan 2016 09:08:29 GMT

Ok. Apart from making any changes like the ones you said using in straight table, isn't there a way to do the same in the script?

Re: Match function issue

dominicmander — Sat, 30 Jan 2016 10:03:03 GMT

‌Ah, I was proposing the straight table as a test only, not as a solution. Could you post a screenshot of the table with all selections cleared? That might help get to the bottom of this.

Re: Match function issue

Anonymous — Mon, 01 Feb 2016 10:13:43 GMT

Hi,

I have attached two tables clearing all the selections. The one on left is what you proposed and Implemented in straight table taking the Rcode as Dimension and Count(%VBELNPOSNR) as expression. The values are still not correct. The one on the right is what I did earlier using the "IF" condition I have used earlier in the straight table as Calculated Dimension and Expression as count(%VBELNPOSNR) which is giving the results as expected.

Re: Match function issue

swuehl — Mon, 01 Feb 2016 10:17:52 GMT

Are you comparing a Count(DISTINCT Field) with a Count(Field)?

Re: Match function issue

Anonymous — Mon, 01 Feb 2016 10:40:53 GMT

Whoops...My bad,

Now that I changed the expression without using Distinct, the values are matching in both the tables. Thanks swuehl and Dominic. But the issue is still the same, I need to get the same values by using the scipt.

Re: Match function issue

Anonymous — Mon, 01 Feb 2016 10:59:12 GMT

Hello Everyone,

Thanks for your help. I have used the following code and checked it in a straight table using what Dominic has suggested and now it seems to be working fine.

Load %VBELNPOSNR,

if(PSTYV='TAB' OR PSTYV='TABN','0 – CrossDock',If(LIPS_WERKS=[WH Code 1],'1 – Cust_Geo_Prime',if(LIPS_WERKS='8041' or

LIPS_WERKS='8042'or LIPS_WERKS='8043'or LIPS_WERKS='8044' or LIPS_WERKS='8615','2 – Sci_Ed',

if(LIPS_WERKS='8031' or LIPS_WERKS='8032','3 – Furniture',

if(LIPS_WERKS='8033' or LIPS_WERKS='8035','4 – Export',

if(LIPS_WERKS='8036' or LIPS_WERKS='8037','5 – Seradigm',if(LIPS_WERKS <> [WH Code 1] AND LIPS_WERKS <> [WH Code 2] AND

LIPS_WERKS <> [WH Code 3] and LIPS_WERKS <> [WH Code 4] and LIPS_WERKS <> [WH Code 5] and

LIPS_WERKS <> [WH Code 6] and

LIPS_WERKS <> '8031' and LIPS_WERKS <> '8032' and LIPS_WERKS <> '8033' and LIPS_WERKS <> '8036' and LIPS_WERKS <> '8037'

and LIPS_WERKS <> '8041' and LIPS_WERKS <> '8042' and LIPS_WERKS <> '8043' and LIPS_WERKS <> '8044'

and LIPS_WERKS <> '8615' ,'6 – AWS_Override',if(%KNMTWERKS=LIPS_WERKS,'7 – Per_InfoRec',if(CLSTK_PLANT=LIPS_WERKS,'8 – Closest_StkPlnt',

IF(DISMM<>'VR' AND DISMM<>'V1' AND DISMM<>'ZA' AND DISMM<>'ZD','9 - NOT_STOCKED',

'10 – Not_Closest_StkPlnt'

)))))))))) AS ReasonCode

Resident Delivery;

Although I have tried the same script earlier, it seems to be working this time. Anyways thank you for all your help. The OR and AND operators are creating a problem for other developers as well in our team. They don't seem to do the operations correctly sometime and later they work fine. I'm not able to figure out why.