en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Thursday, October 27, 2016 10:04:42 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

I have two date columns (column1 and column2) in my data source (from excel) and a new column (column3) which I created in data modeler. Column3 should check if column1 is blank or null, and if it's blank/null it should get it's data from column2.

I can't get to trigger the action if the column is blank, and I have tried several different formulas.

if(isblankorempty(&column1)=TRUE,&column2,&column1)

I've also tried if(&column1=null,&column2,&column1)

and a different approach

if(isblankorempty(&column1)=TRUE,"Field is blank","Field is not blank") -> leaves me only the text "Field is not blank", also when column1 is blank.

Any idea what my problem is?

I have read the available documentation here https://doc.targit.com/e...formats/formulas/logical , it seems like the syntax is some kind of a hybrid between Excel, SQL and Targit's own.

SO  
#2 Posted : Monday, October 31, 2016 7:49:18 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

isblankorempty(&column1) gives me "False" return even if the cell in my excel sheet seems blank and empty. Any idea what my problem is?

How can I check if the cell is really blank or empty? In Excel there are no visible values in these cells.
Peter  
#3 Posted : Tuesday, November 1, 2016 6:50:13 PM(UTC)
Peter
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/1/2011(UTC)
Posts: 43

When the TARGIT Data Discovery tool has detected a datatype as being a date it will be represented by the date 0001-00-00.

Therefore you cannot use tests to see if the value is blank or test for a null value.

You can however check the value of the year by using this formula:

if(year(&DateCol)=1,"Blank","Has A Year!")

The syntax of the expressions are in general Excel format - and is not in any way related to the syntax in the crosstab calculations.

Here's a screenshot with some demo data - I hope this helps you!
https://i.imgur.com/QOuVGTT.png
SO  
#4 Posted : Wednesday, November 2, 2016 2:00:48 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Originally Posted by: Peter Go to Quoted Post
When the TARGIT Data Discovery tool has detected a datatype as being a date it will be represented by the date 0001-00-00.

Therefore you cannot use tests to see if the value is blank or test for a null value.

You can however check the value of the year by using this formula:

if(year(&DateCol)=1,"Blank","Has A Year!")

The syntax of the expressions are in general Excel format - and is not in any way related to the syntax in the crosstab calculations.

Here's a screenshot with some demo data - I hope this helps you!
https://i.imgur.com/QOuVGTT.png


Thanks, that was not easy to guess since it displays as blank. Now it finally works.
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error