Create new data based on existing data

Help Studio
, ,
1

I have a dt with existing rows in which I want to add the same rows again but with modified data. Now the exisiting data is from row 2 to 5 with the region as East. I want to add the dame data for region west but with values of 2% of the original data in the columns 13/2024,14/2024 & 15/2024.

i/p:

image
image778×298 6.7 KB

o/p:

image
image933×340 8.39 KB

Also attaching the test file:
Book1.xlsx (10.7 KB)

2

Hi @SunnyJha

You can achieve this by using LINQ Expression,
→ Use the Read Range workbook activity to read the excel and store in a datatable called dt_Input.
→ Use the assign activity and write the below LINQ Expression,

- Assign -> dt_Output = (From row In dt_Input 
                         Let Package = row(0).ToString 
                         Let Column2 = (CDbl(row(1).ToString) * 0.20).ToString 
                         Let Column3 = (CDbl(row(2).ToString) * 0.20).ToString 
                         Let Column4 = (CDbl(row(3).ToString) * 0.20).ToString 
                         Let Region = "West" 
                         Let ProductLine = row(5).ToString 
                         Select dt_Input.Clone.Rows.Add({Package, Column2, Column3, Column4, Region, ProductLine}) ).CopyToDataTable()

→ Then use the append range workbook activity to append the dt_Output to the same sheet in excel.

Check the below workflow for your better understanding,
DateTime_Practice.xaml (14.3 KB)

DateTime_Practice
DateTime_Practice826×576 37.9 KB

Check the below output file,
Book1.xlsx (8.6 KB)

Hope it helps!!

3

Thanks @mkankatala , this solution is great. Although I have only shown 3 columns for which calculation needs to be performed where as in fact there are 52 columns where I have to do this 2% calculation. Is there any other way to make it more concise?

4

Okay @SunnyJha

For all the 52 columns we have to do the 2% calaculation or for the medium of the columns. Based on the conditions we can change the above expression or we can write the vb expession to achieve it.

If you will provide me the excel then I’ll write the expression for it. But the above the LINQ Expression is works for the attached file only.

Hope you understand!!

5

Book1.xlsx (9.7 KB)
Here’s the sample file with the columns that need the 2% opeartion - 1/2024 to 52/2024. Here when writing to excel the column name 1/2024 got converted to Jan-2024 and it happened till 12/2024 which became Dec-2024. As long as the values are in dt the column names would be 1/2024,2/2024 and so on

6

Okay @SunnyJha

Then the columns are not fixed everytime it keeps changing right?

7

columns are fixed. consider 1/2024, 2/2024…52/2024 as the column names.

8

Hi @SunnyJha

Okay got it, you can use the below LINQ Expression it’s too lengthy but it’s working fine.

I have given you the LINQ Expression in the below Text file,
LINQ Expression.txt (11.9 KB)

Check the below workflow file for better understanding,
Sequence25.xaml (34.9 KB)

image
image855×576 28.9 KB

Hope it helps!!

1 Like
9

as usually it depends on structures and we would dynamize it to avoid heavy hardcodings

eg. like

(From d in dtData.AsEnumerable
Let ra1 = d.ItemArray.Skip(1).Take(X).Select(Function (x) ........)
Let ra2 = d.ItemArray.Skip(x)
Let ra = ra1.Prepend(d(0)).Concat(ra2).Cast(of Object).toArray
......
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.