How to find last month column

Help
, , , ,
1

Hi everyone,

I need to create two variables.

For the first variable;

In the table that I will get this variable, column names go up to F1, F2, F2 … F12. There are also columns that named B1, B2, B3 … B12.

The variable I’m going to write should get data from the B columns of the previous month’s number.

For example, if we are in the 5th month, he should take columns B4. Then i will do the same thing for the columns F.

For the other variable;

It should take the sum of all the columns from the 1st month to the previous month. For example, if we are in the 5th month, the variable should be B1+B2+B3+B4.

Is there anyone who can help me with this?

1 Like
2

Fine let’s go one by one

Once after getting the datatable variable from excel with read range activity use a FOR EACH ROW activity and pass the above variable as input let’s say like dt
—now use a assign activity inside the for each loop like this
str_columnname = “B”+If(Now. AddMonths(-1)ToString(“mm”).Startswith(“0”),Now. AddMonths(-1).ToString(“mm”).Replace(“0”),Now. AddMonths(-1).ToString(“mm”))

Now next to this use a assign activity like this
str_output = row(str_columnname).ToString
This will give the B column numbers value and similarly for F columns as well

And for the next scenario
—next to the above assign activity use another assign activity like this
Int_colindex = dt.Columns.IndexOf(“B”+If(Now. AddMonths(-1)ToString(“mm”).Startswith(“0”),Now. AddMonths(-1).ToString(“mm”).Replace(“0”),Now. AddMonths(-1).ToString(“mm”)))
Same inside the for each row loop use a While loop Like this
counter < int_colindex

Where counter is a variable of type int32 with default value as 1 defined in the variable panel

—Inside this while loop use a assign activity like this
int_sum = Convert.ToUnt32(row(counter))+ int_sum

Where int_sum is a int32 variable defined in the variable panel with default value as 0

Cheers @mazlumkacar

2 Likes
3

Thanks a lot buddy. i think it will work but now there is a problem. When i trying to first step; i have seen this error :
“overload resolution failed because no accessible ‘replace’ accepts this number of arguments”

do you know how may i solve it?

2 Likes
4

This error occurs when we have passed more arguments to a activity
May I know at which activity we get this error
Cheers @mazlumkacar

1 Like
5

Assign activity get this error. I have just try to assign str_columnname to this code

image
image930×234 22.5 KB

1 Like
6

Take the cursor to that blue mark at the right top so that we will come to know the error
Cheers

7

This is the error

image
image1153×757 80.9 KB

Cheers

8

You should try this
use assign activity in for each:
“B”+System.DateTime.Now.AddMonths(-1).ToString(“MM”): it will give you previous month column. for 1st variable.
now for second problem:
now we will use while loop in for each loop counter will start from 1 to convert.toInt32(System.DateTime.Now.AddMonths(-1)ToString(“MM”)).
In while loop:
use assign activity:
variable2=variable2+row.column(“B”+counter)

2 Likes
10

Perfect solution for first part. Thanks for it. But the second part getting this error:(

image
image961×187 29.4 KB

@AkshaySingh

1 Like
11

here you go with the xaml where conditions applied inside tthe FOR EACH ROW loop
columns.zip (9.8 KB)

Kindly try with this and let know ffor any queries or clarification
Cheers @mazlumkacar

1 Like
12

Wow. Perfect solution thanks a lot again :sweat_smile:

The only part I can’t understand; colindex variable returns -1 each time. Is this someting usual:?

@Palaniyappan

13

when the datatable has no such column under that name then the index value will be -1
thats why
and in the while loop include this condition so that we wont face issue with that as well
int_colindex > 0 and counter < int_colindex

Cheers @mazlumkacar

14

Use:
convert.toInt32(System.DateTime.Now.AddMonths(-1).ToString(“MM”)) in while loop.

1 Like
15

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