Nested If statement works in set analysis but not in SCRIPT

[Adamo]

Hi,

The following NESTED IF STATEMENT is working in SET ANALYSIS.

if(WildMatch(NullDate,'*'),'Cancelaciones',
if( WildMatch(ChurnDate,'*'),'Churn',
if( WildMatch(InstallDate,'*') and WildMatch(OldServiceID,''),'Gross Adds',
if(BillingID='0'and WildMatch(NullDate,''), 'Ventas sin confirmar',
if(WildMatch(SalesDate,'*') and not(BillingID='0'and WildMatch(NullDate,'')) , 'Net sales'
))))) 

But is not working in correctly in the load SCRIPT

if(WildMatch(NullDate,'*'),'Cancelaciones',
if( WildMatch(ChurnDate,'*'),'Churn',
if( WildMatch(InstallDate,'*') and WildMatch(OldServiceID,''),'Gross Adds',
if(BillingID='0'and WildMatch(NullDate,''), 'Ventas sin confirmar',
if(WildMatch(SalesDate,'*') and not(BillingID='0'and WildMatch(NullDate,'')) , 'Net sales'
))))) 
as [Tipo Dato],

This is what happens in the APP.

[rwunderlich]

What result do you get in the script?

-Rob

[dwforest]

Is this function being used in a Set Analysis expression? As Set Analysis does not evaluate on a row-by-row basis I would expect differences between a Set Analysis expression and a script If().

Have you determined that the script output is wrong instead of just different?

Would need more information on how this is being used with data and expected output to help futher.

[Adamo]

As you can see in the screen shoot on the first post "Ventas sin confirmar" does not appear as possible value of the dimension "Tipo Dato" whereas in set analysis It does appears.

[ArnadoSandoval]

Hi @Adamo 

I was reading this thread and become intrigue with the expression you are using at the script, the one you posted

if(WildMatch(NullDate,'*'),'Cancelaciones',
if( WildMatch(ChurnDate,'*'),'Churn',
if( WildMatch(InstallDate,'*') and WildMatch(OldServiceID,''),'Gross Adds',
if(BillingID='0'and WildMatch(NullDate,''), 'Ventas sin confirmar',
if(WildMatch(SalesDate,'*') and not(BillingID='0'and WildMatch(NullDate,'')) , 'Net sales'
))))) 
as [Tipo Dato],

This expression will return misleading results when OldServiceID and NullDate are NULLs, because WildMatch returns NULL when its first parameter has a NULL value it returns NULL instead of a logical true/false. 

You should use IsNull(OldServiceID) instead off WildMatch(OldServiceID,'')

I replaced your expression with this one:

     if( WildMatch(NullDate,'*'),'Cancelaciones',
     if( WildMatch(ChurnDate,'*'),'Churn',
     if( WildMatch(InstallDate,'*') and IsNull(OldServiceID),'Gross Adds',
     if(BillingID='0'and IsNull(NullDate), 'Ventas sin confirmar',
     if(WildMatch(SalesDate,'*') and not(BillingID='0'and IsNull(NullDate)) , 'Net sales'
     ))))) As Tipo_Dato

I prepared the following test matrix:

These are the test results; the New_If column matched the Expected results after replacing the WildMatch(Column_Name, '') with IsNull(Column_Name).

Hope this helps,