Match function issue

[Anonymous]

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.

[Anonymous]

PFA Datamodel and Expression in List Box

[dominicmander]

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?

[Anonymous]

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.

[dominicmander · Accepted solution]

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-...'?

[Anonymous]

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?

[dominicmander]

‌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.

[Anonymous]

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.

[swuehl]

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

[Anonymous]

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.

[Anonymous]

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.