0

I am trying to match records and return value. I have DE1 and DE2, in DE1 I have col names "AgencyCode", "AgencyPhone" and "ProducerCode" in DE2 I have "AgencyCode" "ProducerCode" and "AgencyPhone". In DE1 some of agency phone numbers are blank, so for that record I need to print in email Phonenumber from DE2 where AgencyCode and ProducerCode both matches in DE1.

I was thinking to do something like this but I am lost :). How do I match two column values (ProducerCode and AgencyCode)?

SET @A1 = AttributeValue("AGENCYPHONE") 
SET @A2 = Lookup("DE1","AgencyCode","PolicyNumber",PolicyNumber) 
SET @A3 = LookupOrderedRows("DE2",0,"AgencyPhone desc","ProducerCode", @A2)
4

In this instance, I'd use LookUpOrderedRows to lookup on both DE1 and DE2.

Logic:

The first lookup will check if there is any records with matching PolicyNumber, if so then it will grab the AgencyPhone from DE1.

If AgencyPhone is not empty and found in DE1 it will grab the rest of the field from DE1, however if not found it will do a second Lookup on DE2 matching AgencyCode and ProducerCode.

Then if a record is found it will then grab the email and Phonenumber in DE2, assuming the fields you have mentioned are correct.

Sample Code:

SET @A2 = LookupOrderedRows('DE1','1','AgencyCode DESC','PolicyNumber',PolicyNumber)
IF RowCount(@A2) > 0  THEN
    SET @A2Row = Row(@A2,1)
    SET @AgencyPhone = Field(@A2Row, 'AgencyPhone')
    IF NOT EMPTY(@AgencyPhone) AND Length(@AgencyPhone) > 0 THEN
        SET @ProducerCode = Field(@A2Row, 'ProducerCode')
        SET @AgencyCode = Field(@A2Row, 'AgencyCode')
    ELSE
        SET @A3 = LookupOrderedRows('DE2','1','AgencyCode DESC','AgencyCode',@AgencyCode,'ProducerCode',@ProducerCode)
        IF RowCount(@A3) > 0 THEN
            SET @A3Row = Row(@A3,1)
            SET @email = Field(@A3Row, 'email')
            SET @Phonenumber = Field(@A3Row, 'Phonenumber')
        ELSE
            SET @message = 'No record found with matching AgencyCode and ProducerCode in DE2'
        ENDIF
    ENDIF
ELSE
    SET @message = 'No PolicyNumber number found in DE1'
ENDIF
| improve this answer | |
  • Thank you Brad. Its great. Just a one minor change in your code, maybe helpful anyone else looking for same solution. IF RowCount(A3) > 0 THEN will going to be like IF RowCount(@A3) > 0 THEN – Sohail Mar 16 '18 at 13:32
  • Thank you @sohail. Also can you please accept and up vote the solution. Thank you – Brad Sapkota Mar 17 '18 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.