Help with expression-date cast/substring, concatenation and ternary condition

  • 82Views
  • Last Post 20 November 2017
0
votes
Jeremy Piaia posted this 17 November 2017

Hi.

First thing is am not used to SSIS code, I am trying to learn.

The following expression/fields always return " other" which means that first concatenation and last condition do not work

(ISNULL([Date de création])) ? "Error null creation date" : 

SUBSTRING([Date de création],7,4)+"-"+SUBSTRING([Date de création],4,2)+" "+

 

REPLACENULL([Type d'intérêt],"") == "APV" ?

REPLACENULL([Sous-Canal], "APV, ss-canal null")

:

" other"

Where ...

[Date de création] always contains data and looks like "02/10/2017  14:18:00"

[Type d'intérêt] always contains data and looks like "APV"

[Sous-Canal] always contains data and looks like "APV"

 

The source file is downloaded from an external source and every "fields" are in auto mode.

 

Here is how I read it :

If date ... is null, return an error, then ...
concatenate parts of Date de création (i just added a cast to (DT_WSTR,20), just to be sure)
concatenate with result of ...
if(type d'intérêt -never null-) equals APV then return sous-canal (never null)
else return " other"

 

Problems :

  • parts of date are not working, but I am checking if a cast solve the problem
  • Type d'intérêt is mostly equals to APV but it never returns me the Sous-canal value

Am I missing something ?

Thanks

 

0
votes
Mariia Zaharova posted this 20 November 2017

This part of the expression

(SUBSTRING([Date de création],7,4)+"-"+SUBSTRING([Date de création],4,2)+" "+

REPLACENULL([Type d'intérêt],""))

returns the data like this one - 17 1-10 APV

 

So, this will not return TRUE, in most cases it will return FALSE and, thus, you will get "other":

(SUBSTRING([Date de création],7,4)+"-"+SUBSTRING([Date de création],4,2)+" "+

REPLACENULL([Type d'intérêt],"")) == "APV"

 

Please try this expression, probably, this will suit your needs:

(ISNULL([Date de création])) ? "Error null creation date" : 

(FINDSTRING((SUBSTRING([Date de création],7,4)+"-"+SUBSTRING([Date de création],4,2)+" "+

REPLACENULL([Type d'intérêt],"")), "APV", 1)) > 0 ? REPLACENULL([Sous-Canal], "APV, ss-canal null") :"other"

 

Close