Is there a way to use IS NOT NULL as one of the join conditions on a lookup?
I want to get the TOP (1) record where a field is not null, ordered by date.
Currently I have to edit the _Clean stored procedure as custom code to add it, which is obviously not the best way to do things.
Thanks
Best answer by JTreadwell
View original
+5
Hi Mark, Thanks for Posting!
To accomplish this in the Join
Then Edit the lookup field and set the Operator to Top:
Then set the Sorting:
Thanks Joseph that works perfectly.
Is there a way to set the condition to IS NULL too?
+5
Hi Dennis,
Just set the operator in #1 to In list:
+7
Hi Joseph,
won't this approach only work if the field actually contains the string 'NULL' and/or 'null' (depending on collation) instead of being database NULL? TimeXtender will create a query in the cleanse with the following clause: WHERE S.[Dimension_id] IN ('NULL') if you follow your example. This is not the same as WHERE S.[Dimension_id] IS NULL
+5
Hi Rory,
I tested this with the original logic I posted (first comment above) and it worked in the case with database NULLs. Should work. Please let me know if you find otherwise.
Could you please consult someone who really knows SQL.
It doesn't work!
Of course the statement works
tab.col not in ('NULL') for the requirement that only data records that are not NULL and 'NULL' should be selected.
The condition tab.col IN ('NULL') is not the same as tab.col IS NULL.
+7
Hi Joseph,
the Not in list approach works because that filters out NULLs per definition. It also filters records with 'NULL', and other case variants depending on collation, which may not be what you want/expect. In the list below for a source table for the lookup you would expect the result to be 'NULL' (id:2) if you were thinking of an IS NOT NULL approach ordered by id ASC. In your example you would get 'value' (id:3). The In list approach will deliver 'NULL' (id:2), whereas you would want NULL (id:1).
In conclusion: you can use the Not in list approach to filter nulls, as long as you use a list Value not found in the data. However, you cannot implement an IS NULL by using the In list approach.
Note that a similar thing happens if you want to insert a NULL as the result of a transformation: you cannot use a Fixed type with Value NULL as this results in the string 'NULL', you need to use a Custom Value with NULL in the SQL box.
id;value
1;
2;'NULL'
3;'value'
is there a way to lookup a field in other table, but only when another field in the lookup table is empty?
I tried via a join
[Artikel] equal ''
or even
Artikel equal 'NULL'
and even
Artikel equal Artikel with condition Artikel is empty.
Nothing works, only thing left to try is creating second field in lookup table in which I replace the NULL e.g for '-' and then use that in the join. But that means that this field is only created for this specific purpose (in fact transaction table)
+5
Hi Femke
Which join setting are you using in the Lookup? Ideally you should be using the none operator, as MAX, MIN and TOP may make it not work.
I'm using a SUM as I need the invoiced sales amount on a higher level then taken from posted sales transactons.
+5
Hi Femke
Perhaps create a custom view based on the table that you need the field from that creates a sum value and two fields to join with. Then you can map it in as an simple lookup with equal joins.
Hi Thomas, that defeats the purpose of wanting to avoid to maintain1 extra field in the transaction table. So not really an option for me. But thansk for suggesting.
Unfortunately it doesn't work for date types as it tries to convert the 'NULL' (string) to a date to compare it with.
Hi Sven.
Try to clean the data so you don't have null values, by using a field transformation on the field containing the null. (Set it to 1900-01-01 if it is a date field)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
