topic Re: DataFlux 2.6: Compare a field against a list of values from a table in SAS Data Management

DataFlux 2.6: Compare a field against a list of values from a table

mmerz — Fri, 30 Nov 2018 12:02:52 GMT

Hey I’m new to DataFlux Management Studio 2.6 and build so far some data quality controls with the Expression Builder. I use macro variable and the getvar-function to set the value against I compare my dataset. So my control code is fixed and I can still change the values.

Now I have the problem to compare my dataset not only against one value but to list of values. I search a little and found this approach: I insert my comparatives values in a table and try to connect to the database, read the values and then I should compare it with my dataset, but I don’t know how to do this.

I have an odbc-connection to my database ClientData and a table dbo.iso3166 with the column iosalpha2 in it.

So fare from Google-search I try this:

static dbconnection db1
static dbstatement stmt1
dbcursor curs1
boolean retval1

 if isnull(db1) then
   db1 = dbconnect('DSN=ClientData')
if isnull(stmt1) then
begin
   stmt1 = db1.prepare("SELECT ISOALPHA2 FROM dbo.iso3166 WHERE ISALPHA IS NOT NULL")
   stmt1.setparaminfo(0, 'string', 5)
end

 
<<missing comparison>>

 
 
if retval1 == true then
return true
else
return false

Any suggestion how I can fill the missing comparison-section to realize a comparsion between my dataset field "countrycode" and the values from my column isoalpha2?

I'm thankful for any help

Matthias

Re: DataFlux 2.6: Compare a field against a list of values from a table

VincentRejany — Mon, 03 Dec 2018 17:21:50 GMT

Hi Matthias

If you run this code into a data quality control, it means that you will execute a sql query for each check.

The built in test 'Compare a field to a field in a datasource' generates the code below:

static dbconnection db1
static dbstatement stmt1
dbcursor curs1
boolean retval1

if isnull(db1) then
   db1 = dbconnect('DSN=ClienData')

if isnull(stmt1) then
begin
   stmt1 = db1.prepare('SELECT ISOALPHA2 FROM dbo.iso3166 WHERE ISALPHA IS NOT NULL AND ISOALPHA2 = ? ')
   stmt1.setparaminfo(0, 'string', 255)
end
// you will need a field 'ISOALPHA2'
stmt1.setparameter(0, ISOALPHA2)
curs1 = stmt1.select()
retval1 = curs1.next()
curs1.release()

//if the input code is not in the list then we generate a trigger
if retval1 == false then
   return true
else
   return false

Re: DataFlux 2.6: Compare a field against a list of values from a table

mmerz — Tue, 04 Dec 2018 19:42:53 GMT

Hi Vincent,

thanks for your replay.

I tried your code but unfortunately I got an error massage.

I made some negative-tests and I think my dataconnection is fine and my list has also entries.

Any suggestions what the error caused?