0

when I try to compare two dates in lookuprows to receive rows which values in the column 'Available_From__c' are bigger than the value in variable @moveindate, results in this error. >

The Data Extension Key Value for a LookupRows function call is invalid. The value cannot be converted to the type of the key column. Function Call: LookupRows('Apartment_Master_Homelike','City__c',@City__c, 'Number_of_bedrooms__c', @Number_of_bedrooms__c,'Has_Availability__c','true','Online_and_verified__c','true','Available_From__c','@appartment_available > @moveindate') Data Extension Name: Apartment_Master_Homelike Key Field Name: Available_From__c Key Field Type: Date Time Key Value: @appartment_available > @moveindate

Here is the Lookuprows function.

LookupRows('Apartment_Master_Homelike','City__c',@City__c, 'Number_of_bedrooms__c', @Number_of_bedrooms__c,'Has_Availability__c','true','Online_and_verified__c','true','Available_From__c','@appartment_available > @moveindate')

The variable @moveindate is a date from another data extension. For testing, I also replaced @moveindate with a specific date which resulted in the same error.

Has anybody an idea on how to solve this?

2

The issue is that Lookup functions in AMPscript cannot do anything other than match exact values.

So the 'value' of @appartment_available > @moveindate is invalid

You have two options at this point:

  1. Utilize SSJS - via the Rows.Retrieve function (this is fairly slow in processing - not recommended to be used in an email.)
  2. Do an AMPScript lookup without the date part - pulling ALL of the dates and then doing a FOR loop and IF statement to filter out the correct rows.

Sample of SSJS:

var rowsDE = DataExtension.Init("Apartment_Master_Homelike");
var complexfilter = {
                LeftOperand:{
                    Property:"Your Other Stuff",
                    SimpleOperator:"equalTo",
                    Value: "Your Other Values"
                },
                LogicalOperator:"AND",
                RightOperand:{
                    Property:"appointment_available",
                    SimpleOperator:"greaterThan",
                    Value: moveindate
        }};
var moredata = rowsDE.Rows.Retrieve(complexfilter);

Sample of AMPScript:

SET @Rows = LookupRows('Apartment_Master_Homelike','City__c',@City__c, 'Number_of_bedrooms__c', @Number_of_bedrooms__c,'Has_Availability__c','true','Online_and_verified__c','true')

FOR @i TO ROWCOUNT(@Rows)

  SET @Row = ROW(@Rows, @i)
  SET @appartment_available = Field(@Row, "appartment_available")

  IF @appartment_available > @moveindate THEN

    /* YOUR ACTIONS HERE */

  ENDIF

NEXT @i
]%%

You could also utilize the API - but I would only recommend this if you plan on returning more than 2500 records.

  • Always glad to help :) – Gortonington Dec 21 '17 at 15:05

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.