Compare data does not get all data

CloverDX Platform
1

I have been struggling with this issue for a couple of days now and have not found any reason why this does not compare the data correctly.

I have a graph that calls a sub graph to compare user input from a file and data in the data base. The file contains the field name to compare, what to use as the comparison and the value to compare to. The graph will read this file then use a Resolve_Criteria function that imports a .ctl file containing the switch case below to compare the records. It seems to work when using all cases except “<=” and “>=”. When using these cases it does not get all of the possible records.

In my data I have a field called company_score that can contain an integer of 1 through 100 that I am comparing with a file that can be edited for various comparisons. To test I have filtered out all data below 85 to limit the returned records set to only a few hundred records. In my file I set the parameters to compare the records in company_score and only return those that are >= 90. I would expect that this would return all records that have a value of 90 to 100. What I get is only 90 to 99. For some reason it does not get the records that contain 100. When I compare all of the data in the source I will also get records with a value of 9, 1 and 85 to name a few.

Is using the format I have below the cause? I have researched using the “compare” function but the examples in the documentation are not clear to me as to how to use it.

Here is the case statement:


	switch(rec.operator){
		case '=' : if (strVal == comparedToValue) { return true;} break;
		case '!=' : if (strVal != comparedToValue) { return true;} break;
		case '<=' : if (strVal <= comparedToValue) { return true;} break;
		case '>=' : if (strVal >= comparedToValue) { return true;} break;
		case 'is empty' : if (isBlank(strVal)) { return true;} break;
		case 'is not empty' : if (!isBlank(strVal)) { return true;}break;
		default: raiseError('Unrecognized operator: ' + rec.operator); 				
	}

This also works with string or date comparisons so I am not limited to only integer data types.

Here is the full ctl code that is used.


/**
	* Resolve Criteria by type
	* List all defined criteria for a type, check them sequentially
	* Expects filled CriteriaLkp
	* Format of the CriteriaLkp
	* Criteria type,Criteria ID,CheckField,Operator,Value
	**/
function string resolveCriteria(string CriteriaType){	

	Criteria lkpRec; 
	map[string, Criteria] criteriaMap;
	string returnValue;
	integer idx = 1;
	
	//lookup doesnt keep order of inserting, we need to lookup by type and priority
 	lkpRec = lookup(CriteriaLkp).get('ContactPriority', idx);
	while(lkpRec != null) {	
		idx++;	
		
		string inputVal = trim(getValueAsString($in.0, lkpRec.checkField));
		string valueToCompare = trim(nvl(getResolvedDateStr(lkpRec.value), ''));
		
		switch(lkpRec.operator){
			case '=' : if (inputVal == valueToCompare) {  return lkpRec.criteriaID;} break; 
			case '!=' : if (inputVal != valueToCompare) { return lkpRec.criteriaID;} break;
			case '<=' : if (inputVal <= valueToCompare) { return lkpRec.criteriaID;} break;
			case '>=' : if (inputVal >= valueToCompare) { return lkpRec.criteriaID;} break;
			case 'is empty' : if (isBlank(valueToCompare)) { return lkpRec.criteriaID;} break;
			case 'is not empty' : if (!isBlank(valueToCompare)) { return lkpRec.criteriaID;}break; 			
		}
		
		lkpRec = lookup(CriteriaLkp).get('ContactPriority', idx);
	}

	return returnValue;
}

function string getResolvedDateStr(string checkValue){	
	if (contains(checkValue,'days')){
		integer days = str2integer(replace(nvl(checkValue, '0'),'[^0-9]', ''));
		if (contains(checkValue, 'plus')){
			checkValue = date2str(dateAdd(today(), days, day), 'yyyyMMdd');
		}else{
			checkValue = date2str(dateAdd(today(), - days, day), 'yyyyMMdd');
		}
	}
	return checkValue;
}

/***
	* One step of resolving criteria
	* Checks input record rec.checkField (eg. $in.0.phase) for rec.Value
	* Returns true if expression is true
	* If rec.Value contains string 'days', it flips into date version of comparison
	* Dates are compared by transforming them into strings and then compare (yyyyMMdd)
	*/
function boolean resolveCriteriaWorker(Criteria rec){
	string strVal = trim(getValueAsString($in.0, rec.checkField));
	boolean result = false;
	
	string comparedToValue = trim(rec.value);
	if (contains(rec.value,'days')){
		strVal = date2str(today(), 'yyyyMMdd');
		comparedToValue = nvl(getResolvedDateStr(rec.value), '');
	}
	

	switch(rec.operator){
		case '=' : if (strVal == comparedToValue) { return true;} break;
		case '!=' : if (strVal != comparedToValue) { return true;} break;
		case '<=' : if (strVal <= comparedToValue) { return true;} break;
		case '>=' : if (strVal >= comparedToValue) { return true;} break;
		case 'is empty' : if (isBlank(strVal)) { return true;} break;
		case 'is not empty' : if (!isBlank(strVal)) { return true;}break;
		default: raiseError('Unrecognized operator: ' + rec.operator); 				
	}
	return false;
}

My question is why does this not return all the possible records and why does it also include records that are not greater than 90?

Thank you in advance.

2

It looks like you are comparing not numbers, but strings - you have your variables defined as string variables. When comparing strings, you get “100” lover than “90” simply because it looks at the first chars of each string “1” & “9” in this case and decides that 1 is less than 9.
In order to fix it, covert your string representation of value to a number first (eg. str2integer() function) and then do the comparison.

3

Thank you that worked.

Now I need to modify this so that it will work for both a string and integer.