If match condition is not showing the required

[Anonymous]

Hello,

Can someone please help

I have the following script:

Load CEMRDCD_HS,

LEGETYNACE7_BK,
CPT_LEID,

ORGNOTAMTEFFUDLYMAT_HS,
EXPLRISSGAR_HS,
EXPLRFRLIM_HS,
If(match(LEGETYNACE7_BK,'6511001','6511002','7511001','7511006'), 'SOVEREIGNs-S7',
If(match(LEGETYNACE7_BK,'7511002','7511003','7511004','7511005','7512100','7512200','7512300','7513000','7514000','7521000','7522000','7523000','7524000','7530100','7530200','7530300','7530400','7530501',
'7530502','7530503','7530600','7530700','7530800','7530900','9111200','9112200'),'PSEs-S8',
If(match(LEGETYNACE7_BK,'6512101','6512102','6512103','6512104','6512105','6512107','6512300','6512400','6512500','6512701','6512702','6512703','6512801','6512802','6512900','6522000','9900301'),'FIN INSTs-F1',
If(match(LEGETYNACE7_BK,'6512100','6512600','6512601','6512602','6512700','6512800','6512106','6512200','6521000','6523100','6523101','6523200','6523300','6523301','6523302','6523303','6523102','6523103','6602000',
'7415203','7415204','6601100','6601200','6603100','6603200','6603300','6712000','6713000','6713001','6713002','6713003','6720100','6720200','6720300','6720301','6720302','6720303',
'7484702'),'OTHER FIN INSTs-F3',
If(match(CPT_LEID,'18834','35534','18816','18849','82882','25673','379142','18810','26874','20384','51155768','18813','18848','193048'),'MDB-S3',
If(match(CPT_LEID,'11187','87667','10796','10879','10802','10867','10733','231076','183267','10874','10798','10834','235271','176851','232722','325223','318879','31139503','31248594','92959078','92961484','603950'),
'GSEs-F5'
)))))) as Counterparty_type
FROM

(txt, codepage is 1252, embedded labels, delimiter is '~', msq);

The result Counterparty type doesnot show GSEs-F5 even though there are values. for instance '11187' is present.

Why doesnt  counterparty_type show GSEs-F5. is there something wrong with the code.

Here is the screen shot

[swuehl]

For this specific input record, can it happen that there is a match for LEGETYNACE7_BK, so the if() statement will not reach the part checking CPT_LEID?

[marcus_sommer]

I think it would be easier to use mapping instead of this nested if-loop, see here what is meant: Mapping … and not the geographical kind

- Marcus

[#4]

Hi Paul,

Your problem probably happened because the record '11187' has LEGETYNACE7_BK value that has entered another if before arriving in this last clause, I suggest you to filter the '11187', see what it returns in LEGETYNACE7_BK column and then compare to see if there is a rule for the return on your if

Best regards

Marcos Freire

[Anonymous]

Thank you all.

Yes  i have fitlered and checked. I see for all the GSE CPT_LEIDs there is a corresponding LEGETYNACE7_BK.

Is there any other function which i could use to display. can you please help ?

Regards,

Paul

[marcus_sommer]

What I notice now is that you are checking those field-values against strings - numbers within quotes - and at least the field CPT_LEID looked numeric (left aligned values). Also your nested if-loop has no end else-part which made it more difficult to find not suitable matching - I mean something like this:

GSEs-F5', '#NV'
)))))) as Counterparty_type

- Marcus

[swuehl]

Paul, not sure if we are on the same page.

You need to look at your Input records.

Example

RecID, Field1, Field2

1, A, B

If you then have a LOAD Statement like

LOAD Field1, Field2,

        if(Match(Field1, 'A'), 'Ha',

            if(Match(Field1,'B'), 'He',

                if(Match(Field2,'B'), 'Hi'))) as Flag

FROM ...;

What do you think will be returned as Flag for this Input record? Even though there Field2 matches 'B'?