Error while comparing Dates in a lookuprow function

Question

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?

Answer 1

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.