Issue with a in-database filter

[Thomas_simonet]

Hi everyone, 

I'm facing a issue when comparing multiple fields with a in-db filter.

I'm wanting to compare multiple fields from two tables, to make sure the values are identical. Alteryx connects to a Hive db, makes a join between the two tables, then I'm using filters to compare the values. I have formulas like this : `field` != `R_field`. For some fields it works well, but for others I don't get any line either in the true or false results (I have some browse tools for each output). How it this possible ?

The left field is a string, which contains dates like "2017-09-19 00:00:00.0", the right field is a Date where the value is "2017-09-19". It should be equal, right ? I have some other date fields where the comparison works and Alteryx says they are equal (between String and DateTime). In fact this test is supposed to make sure the dates are still the same after another workflow makes the conversion.

So where am I mistaken ? I can provide more explanations and screenshots if needed.

Thanks for your help.

[cmcclellan]

Although this isn't the exact answer to your question, I hope it helps in some way .... 

With the normal filter tool, everything that equates to TRUE (or 1) is output from T and everything else is output from F

With the InDB Filter, 1 is output from T BUT FALSE (or 0) is output from F.  If your condition equates to NULL, the record is not output at all.

[vishwa_0308]

Just for a try could you please cast your left input string to date in in-db filter:

cast([leftfield] as date)!=[RightField].. hope it works :)

[Kaviyarasan_P]

“For some fields, it works well, but for others, I don't get any line either in the true or false results (I have some browse tools for each output)” 

If the condition is not satisfied then it should be in the false result. There is no null() results in the filter. There may be a problem in the condition, therefore, it won’t give any result but you will get the warning message

“Alteryx says they are equal (between String and DateTime)”

If a date/time value is not in "yyyy-mm-dd HH:MM: SS" format, Alteryx reads it as a string. Where did Alteryx say as both are equal? I didn't find anywhere.

“The left field is a string, which contains dates like "2017-09-19 00:00:00.0", the right field is a Date where the value is "2017-09-19". It should be equal, right? I have some other date fields where the comparison works”

"2017-09-19 00:00:00.0"  in given example there is “.0” value at the end. Therefore you are not getting a result.           

[Thomas_simonet · Accepted solution]

@vishwa_0308 Thanks, I just tried, it didn't work at first but it works when adding a substring to keep only the date characters :

cast(substr(`ddpa`,0,10) as Date) != `R_ddpa`

In this case I get my lines in the false output (which means it's not different, so it's equal).

Maybe it can't compare between the String and the Date because the String is under a "DateTime" format... I know it works between a String and a DateTime when the String is under a DateTime format (I hope it's clear haha).

[Thomas_simonet]

---

@Kaviyarasan_ wrote:

“For some fields, it works well, but for others, I don't get any line either in the true or false results (I have some browse tools for each output)” 

If the condition is not satisfied then it should be in the false result. There is no null() results in the filter. There may be a problem in the condition, therefore, it won’t give any result but you will get the warning message

“Alteryx says they are equal (between String and DateTime)”

If a date/time value is not in "yyyy-mm-dd HH:MM: SS" format, Alteryx reads it as a string. Where did Alteryx say as both are equal? I didn't find anywhere.

“The left field is a string, which contains dates like "2017-09-19 00:00:00.0", the right field is a Date where the value is "2017-09-19". It should be equal, right? I have some other date fields where the comparison works”

"2017-09-19 00:00:00.0"  in given example there is “.0” value at the end. Therefore you are not getting a result.           

---

Apparently like I said in another reply, if it can't compare, there is no true nor false output. There was no problem in the condition (I checked many times). And I don't have any warnings.

The left string is in "yyyy-mm-dd HH:MM: SS.0" format, and the right Date is in "yyyy-mm-dd" format (previously converted with a cast as Date in another workflow). It doesn't seem to work, but it can compare between a "yyyy-mm-dd HH:MM: SS.0" String and a DateTime format though.