Dataflux data management studio - data filtering problem

[Atte51]

Long-time fluxer, first-time poster

Hi all! I desperately need advice with the following issue:

I have a problem with DataFlux Data Management Studio related to SAS Base-based tables. I would like to be able to filter data in the same way as with SQL queries, but I have no idea how to achieve this using the EEL language.

Simply put, my dataset includes data from different reporting months, and I would like to be able to filter the data based on various conditions, for example, according to different report dt. Additionally, the information should be retrieved from each row, not just a single max report dt from the table. 

Here's an example  of dataflux SQL query where we get the max report dt and the next largest report dt (-1) (just to give you an idea of what I'm looking for):

example.REPORT_DT = (Select MAX(REPORT_DT) from example)
example.REPORT_DT >= DATEADD(day, -1, CAST(GETDATE() as date))

All ideas and suggestions are welcome 🙂

Br,

Atte

[audrey]

Hi,

I am not sure I completely understand what you're trying to achieve. If you can give an example of the data you have in input and the output you expect, I can take a look at what can be done with the Expression node.

Audrey

[Atte51]

Hello Audrey!

Certainly! So, as you can see in the picture, I have daily data on a table, and I would like to be able to retrieve the maximum value and possibly the next value from these maximum values.

I am creating data quality rules that require examining only the latest values. In the data on the table, there is no variable that indicates a "valid" value. The only way to retrieve the latest values is the newest row in the table based on the report_dt. I also have a need to occasionally compare the differences between values for different dates, so I would like to be able to retrieve the next highest value (-1) from the data. 

[audrey · Accepted solution]

I don't think there's anything pre-build to calculate the max of a list of records. The max function exists in EEL but will take values as arguments, so not what you need.

You can try to use some simple code in the Expression node 

//Pre-Expression 
//Setting variables
boolean isMaxVal;
hidden integer prevMaxVal;
isMaxVal= false;
prevMaxVal=0;

//Expression
if `Frequency` >= prevMaxVal
begin
   isMaxVal = true;
   prevMaxVal=`Frequency`;
end
else 
   isMaxVal=false;

This might require your data to be sorted though.

The same logic could apply to the latest value. 

Does this help?

[Atte51]

Hi Audrey,

This worked really nicely, thanks very much!

-Atte

[VincentRejany]

Hi 

In the SQL Query Node, you can enter SQL that is specific to the data source that is selected in the Data Source field above

If you selected a SAS table that is accessed with a SAS Federation Server connection or a SAS Data Set Connection, you can enter Federation Server FedSQL, which is described in the SAS Federation Server FedSQL Reference Guide. If you selected a database table that is accessed with an ODBC connection, you could enter database-specific syntax. For more information, see the ODBC help in DataFlux Data Management Studio and also the documentation for the database.

I tested successfully these queries

select a.* from SAS.ORDERS a where delivery_date = (select max(b.delivery_date) from SAS.ORDERS b)

select * from SAS.ORDERS where delivery_date >= today() - 1

[Atte51]

Hi VincentRejany!

To my knowledge, the SQL node cannot be used with SAS base tables. At least, that has been my understanding. This solution solved the problem immediately, but as far as I know, I have to use the data source node with SAS base tables, where there is no SQL capability.