Value Divided id present not

[Shyam_Pragash]

Hi…

i have to divided the col1, col2, col3, col4…etc.,

=col1/100000

i done it on C# code… same issue in that code… only one columns passed at a time…

How pass many columns in this code?

my code :

(From row In Dt_Read.AsEnumerable()
Let colInd = Dt_Read.Columns(str_colName).Ordinal
Let col = If(IsNothing(row(str_colName)) OrElse String.IsNullOrEmpty(row(str_colName).ToString),"0", row(str_colName))
Let div = Double.Parse(col.ToString,System.Globalization.CultureInfo.CurrentUICulture)/100000
Let ra = row.ItemArray.Take(colInd).Append(div).Concat(row.ItemArray.Skip(colInd+1)).ToArray()
Select Dt_result.Rows.Add(ra)).CopyToDataTable()

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

You want to do it row by row for every column /1000 is that correct ?

Thanks
#nK

[Shyam_Pragash]

Hi @Nithinkrishna

Yes… I have many row to divide into “100000” if cell is null, the cell update to “0” and divide it…

do it many columns…

Thanks
Shyam

[Shyam_Pragash]

Hi @Nithinkrishna

Any Update ??

[Shyam_Pragash]

Hi…

tried in For Each Row Activity

i have to use more 30 assign activity (Each columns) use to divide the value and write to the Columns Name.

Done it for Single Columns Only.

First Assign:
CheckCond = If(IsNothing(CurrentRow(str_colName).ToString) OrElse String.IsNullOrEmpty(CurrentRow(str_colName).ToString),“0”, CurrentRow(str_colName).ToString)

Second Assign:
Currentrow.item(“Str Col Update”) = Double.Parse(CheckCond.ToString,System.Globalization.CultureInfo.CurrentUICulture)/100000

Can tell me how to simplify this assign activity…

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

No need of Assign It can be directly done with LINQ

So just confirming to write the query,

You want to divide the column by 100000 if it has value else mark it as zero right ?

Thanks
#nK

[Shyam_Pragash]

Hi… @Nithinkrishna

Your are right

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

Here it is,

PFA - nmnithinkrishna_ExcelColumnManipulation.zip (13.0 KB)

Hope this helps you.

Thanks
#nK

[Shyam_Pragash]

Hi @Nithinkrishna

Where i declare the columns name… The columns are not conitune one…

i have to address the columns to be divide…

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

Currently the above code will change it for entire table !

If you can explain the column specific scenario please, we can make it !

Mostly a small tweak in the above will do the job.

Thanks
#nK

[Shyam_Pragash]

Hi @Nithinkrishna

Input Data: (Shared few rows and columns)

Expected Output:

in above input data file selective columns only (Total Cost, Total Profit) i have to divide to 100000. i have many columns

Second requiremnt…

Selective columns only, i have to change to decimal place two digit

Total Cost	Total Profit
15.82	9.51
0.00	0.00
9.34	2.25
0.56	0.20
26.57	6.39
0.00	0.00
21.04	6.94

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

Please find the updated !

nmnithinkrishna_ExcelColumnManipulation_v2.zip (18.3 KB)

Thanks
#nK

[Shyam_Pragash]

Hi @Nithinkrishna

i want two decimal points… it shows three digits…

can you share decimal points coding seperatly…

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

Oh, the beauty of untested code

Here it is,

Decimal.Parse("23.4556").ToString("0.00")

Else you can go for truncate as well !

I hope you understand the place where we do this in the query, so that once you figure the decimal logic you can just place it to work.

Thanks
#nK

[Shyam_Pragash]

Hi @Nithinkrishna

Thanks

[Nithinkrishna]

Cool @Shyam_Pragash

[Shyam_Pragash]

Hi @Nithinkrishna

i have one issue…

Decimal value convert to two digit value …

Same value reflected as you said code used…

This is One columns Output

Thanks
Shyam

[Nithinkrishna]

Hey @Shyam_Pragash

Just thinking if this is due to the Excel ?

Could you please do it as a dummy test sequence to see if that works for one Excel cell ?

Thanks
#nK

[Shyam_Pragash]

@Nithinkrishna

Decimal.Parse("23.4556").ToString("0.00")

Hi @Nithinkrishna

Your shared decimal code is working fine… individually to run it…

Yesterday you share one code and deciamal place

nmnithinkrishna_ExcelColumnManipulation_v2.zip (18.3 KB

Three digit Decimal place shows… i want to only two places…

dt_Table.AsEnumerable.Select(Function(row) dt_TableFormatted.Rows.Add(row.ItemArray.Select(Function(item, index) If( ({"Col A","Col B","Col C","Total Cost","Total Profit"}).Contains(dt_Table.Columns(index).ColumnName), If(String.IsNullOrEmpty(Convert.ToString(item)), 0, CInt(item)/1000 ), If( ({"Col A","Col B","Col C","Total Cost","Total Profit"}).Contains(dt_Table.Columns(index).ColumnName), Decimal.Parse(item.ToString).ToString("0.00"), item))).Cast(Of Object).ToArray)).CopyToDataTable

Thanks
Shyam

[Nithinkrishna]

Yep @Shyam_Pragash

Looks like decimal formatting not working inside query.

Could you please try Math.Truncate which may help.

Thanks
#nK