Datatable calculation from Excel values and Web application values

Help Studio
, ,
#1

Hello

In my scenario, after creating a datatable from excel extract I needed to add 4 data columns (L, M, N, O). The values of newly added data columns are calculated from previous columns by applying certain formulas or getting values from web application.

  1. column L is calculated based on (I-J)*-1. Since I and J columns may have spaces, I am not able to apply the formula. What conversion would be required.
  2. Column M values(numeric) will be retrieved from web application. This will happen later in workflow. However this value need to be used to calculate column N.
  3. Column N is calculated L+M. - How should I handle this. values of L is from data table and values of M is from web application ( later in workflow)
  4. Column O - from web application alpha numeric, not used for any calculation.

Thanks for the help.

Best Regards

#2

Use Add data column activity first to insert the columns.

you can use this expression in the assign activity

YourDatatableVar.Columns(“ColumnN Name”).Expression = “ColumnLName + ColumnMName”

Note: If ColumnL Name has spaces you have to surround with the square brackets…
For Ex: “[First Name] + [Last Name]”

Similar try it on the others bullet points and let share if you get any errors…

#3

Thanks Guru. recommended solution did not resolve the issue. 1st bullet has to be calculated before 3rd in order to capture column L values.
How do I handle where one value is coming from Excel and other value is coming from web application in this formula. Here web application steps are not processed yet.

#4

Hi @Shobhit_Gupta … It would be quite difficult without seeing whats in column J.

If you have more spaces then you have to cleanup first before applying formula…like using replace or trim or Regex…

In this case, you may need to finish write everything from the web app first before proceeding the calc step.

#5

Mainly Column I and J has numeric values. but there are instance row values are blank.

Once I have column A and Column C value from excel, I have to pull a record from web app. from that record, I will collect value for column M and Column O. Thereafter column L ((I-J)*-1) and column N(L+M) will be calculated. This need to be done for each record in excel.

#6

@Shobhit_Gupta - it would be great…if you could share some sample(masking or dummy data) excel sheet and screenshot with us…

#7

Excel Template.xlsx (8.9 KB)

Attached is the sample data. Column L to O will added to this file.
Column L value will be created using formula (I-J)*-1
Column M will be captured from web app ( numeric values)
Column N value will be calculated using formula L+M
Column O will be captured from web app.

Thank you.

#8

@Shobhit_Gupta - Is this the result you are looking in Column L?

image
image954×393 5.85 KB

Question: Can you do J-I instead…

Apologize, formula in the first post just concatenates the column…so pls ignore…

#9

Calculation is correct but formula has to be (I-J)*-1

#10

@Shobhit_Gupta - That is what is there…i just showed the formula in excel…But you didnt answer my question yet??

#11

no it cant be J-I

#12

@Shobhit_Gupta - One idea i am thinking is…First you have to replace all the spaces with 0 in column I and then simply using write cell in column L (assuming you dont have lot of rows) with forumula and use AutoFillRange it will automatically copies the formula to the subsequent rows…

@NIVED_NAMBIAR - Any thoughts…using Invoke Code…???

#13

Hi @prasath17

So u mean to replace all the spaces / empty cells with 0 in overall?

#14

Column I and J …As per @Shobhit_Gupta

#15

What he mean by spaces is it empty or there is any space before or after any character?

#16

@NIVED_NAMBIAR - I guess like this…

image

@Shobhit_Gupta - Please add your comments…

#17

Try with trim ?

#18

@NIVED_NAMBIAR @prasath17 : These are just empty/blank cells. No spaces.

#19

@Shobhit_Gupta
You need to perform following operations on your data variable
1. Add 4 columns and give them name as per your requirement
2. For each row
Use 4 assign

  1. dt(“Column L”)="=(I2-J2)*-1"
  2. dt(“Column M”)=“NumericValuefromAPP”
  3. dt(“Column N”) = “=L+M”
  4. dt(“Column O”)=“Captured Value from webapp”

This will append formulas on your datatable and not actual values.
Any information that you change on other column will impact your data

#20

@Shobhit_Gupta - please find some starter help here…Just to let you know, it may not be perfect…but at least this will give an idea for you to proceed further…

Datatable calculation_SG.zip (44.8 KB)

Hint from the workflow…

image
image544×604 14 KB

Output

image
image1226×353 25.6 KB

Hope this helps…