IF Statements in Expression

  • 125Views
  • Last Post 2 weeks ago
0
votes
Ben Willis posted this 15 June 2018

Is it possible to use if statements in an Expression Mapping when doing a data import.

For example:

 

IF(Field1 = "X","AB",
IF(Field2 ="Y","ABC",
IF(Field3 = "Z","ABCD",
A
)))

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 15 June 2018

Hi Ben!

Please refer to:

https://skyvia.com/resources/docs/index.html?expressions.htm

https://msdn.microsoft.com/en-us/library/ms141680.aspx

 

For example, IF Expressions can be written like this: 

Field1 == "X" ? AB: ABC

Field1 == "X" ? "AB" : (Field2 == "Y" ? "ABC" : "A")

 

If you have any further questions, feel free to contact us.

 

Best regards, 

Mariia

0
votes
Laurent Michel posted this 3 weeks ago

Hi, 

I have the same question just to be sure :

I have on a csv Colomn C Type designation and B Code designation 

I have 8 conditions to complete 8 differents fields on salesforces

Like on mapping to the Siret field on salesforce :

If colomn C = SIRET i want to search Colomn D 

On mapping to the UAI field on salesforce : if colomn D = UAI i want to search colomn D 

... for 8 field 

 

Thank you for your help

 

0
votes
Laurent Michel posted this 3 weeks ago

Hi, 

I have the same question just to be sure :

I have on a csv Colomn C Type designation and B Code designation 

I have 8 conditions to complete 8 differents fields on salesforces

Like on mapping to the Siret field on salesforce :

If colomn C = SIRET i want to search Colomn D 

On mapping to the UAI field on salesforce : if colomn D = UAI i want to search colomn D 

... for 8 field 

 

Thank you for your help

0
votes
Dmitriy Muzurov posted this 3 weeks ago

Hi Laurent,

Thank you for your message.

It seems you need to configure a lookup in expression which allow you to get data from destination object. Unfortunately, it is not possible in Skyvia mapping.

When importing from CSV a lookup can look for values of current record in source only, target lookup is not available.

Should you have any questions, do not hesitate to contact us back.

0
votes
Laurent Michel posted this 3 weeks ago

Hi

 

Ok thank you for your answer, on my csv source i have the data, the "expressions" if is not available for CSV ? 

how can i do this type of things ? i need to have many fields on my csv ?

0
votes
Dmitriy Muzurov posted this 3 weeks ago

Dear Laurent,

Thank you for reply.

In order to be able to advise something we need more details about your scenario.

Please clarify the following points:

1) What is the structure of your CSV file? What columns does it have?

2) What is the structure of the destination object? Specify its columns.

3) What should be the expected result? We would appreciate if you provide us with more details about the mapping, its logic and values of columns you want to migrate. 

We are looking forward to your reply.

0
votes
Laurent Michel posted this 2 weeks ago

Hi,

Ok thank you, the link to the csv https://fromsmash.com/eg-sf-s 

1 ) structure on the first row you have the fields names on CSV and after on line 3 the name on salesforce  :

So the external id key is CLIENT.CDCLI on CSV and Code_client_c on salesforce

For the designation on csv it's on the same cells but on salesforce we don't have this we just have 8 separet fields with the name similar to designation et id 

And the CIENT.CDID its the id for the designation, so if i have on the csv designation = SIRET and CLientcdid = 555555555 on salesforce we to insert on the field SIRET the ID

If i have UAI and 444568 i want to insert on salesforce 

So the typeid designation it's juste to 

 

2 ) It's custom object with the name identifiant_c i created a task on skyvia to show you :

Upsert Identifiant__c

 

0
votes
Dmitriy Muzurov posted this 2 weeks ago

 Dear Laurent,

Thank you for details.

It is possible to implement your scenario by specifying an expression mapping for each of your 8 fields.

The expression will look like this:

[TYPEID.DESIGNATION]=="SIRET"?[CLIENT.CDID]:NULL(DT_NUMERIC,10,3)

For instance, we specify this expression for "SIRET__c" column. Then in this column the value of CLIENT.CDID column will be inserted, if TYPEID.DESIGNATION is equal to SIRET. 

For all other columns in the destination object a NULL value will be inserted, in other words they will be left empty.

Such expression type should be specified for each necessary field and the word "SIRET" should be changed to a TYPEID.DESIGNATION value that is appropriate to a certain field in the destination object.

Also, the datatype of a NULL function should be changed to the appropriate datatype of the destination field. Here is the article for reference: https://docs.microsoft.com/en-us/sql/integration-services/expressions/null-ssis-expression?view=sql-server-2017

Should you have any questions, do not hesitate to contact us back.

0
votes
Laurent Michel posted this 2 weeks ago

Ok thank you !

I tried but it's not working i have this message :

The data types "DT_WSTR" and "DT_NUMERIC" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

I just need to change by TEXT ?

1
votes
Dmitriy Muzurov posted this 2 weeks ago

Hi,

We are sorry for confusion.

Here is the working sample of expression that we tested:

[TYPEID.DESIGNATION]=="SIRET"?[CLIENT.CDID]:NULL(DT_WSTR,40)

We are looking forward to hearing from you, should you have any questions.

  • Supported by
  • Laurent Michel
0
votes
Laurent Michel posted this 2 weeks ago

Hi,

 

Perfect it's working well !

Close