Change value in Column based on column name

Help Studio
, , , , , , , , , , ,
1

Hi Team,

We have a sheet which is in this format

Year|Month| Bolivia|India| New Zealand
2021|1 |2800 |6800 |1100
2021|2 |3800 |6800 |2100
2021|3 |4800 |9800 |3100
2021|4 |5800 |8800 |4100
2021|5 |6800 |8800 |5100
2021|6 |7800 |8800 |6100
2021|7 |8800 |6800 |7100
2021|8 |9800 |4800 |8100
2021|9 |9800 |4800 |9100
2021|10 |6800 |3800 |1100
2021|11 |6800 |1800 |2100
2021|12 |5800 |2800 |6100
Total
2022|1 |1800 |6800 |2100
2022|2 |2800 |4800 |3100
2022|3 |3800 |3800 |6100
2022|4 |4800 |2800 |8100
2022|5 |5800 |1800 |9100
2022|6 |6800 |8800 |8100
2022|7 |7800 |9800 |7100
2022|8 |8800 |9800 |6100
2022|9 |9800 |5800 |5100
2022|10 |1800 |3800 |4100
2022|11 |1800 |1800 |3100
2022|12 |1800 |2800 |2100
Total
2023|1 |800 |8020 |9100
2023|2 |8000 |8003 |8100
2023|3 |3000 |8003 |7100
2023|4 |8090 |8005 |6100
2023|5 |8008 |8060 |5100
2023|6 |8003 |8080 |4100
2023|7 |2800 |3800 |3100
2023|8 |3800 |8200 |2100
2023|9 |2800 |2800 |1100
2023|10 |8100 |1800 |2100
2023|11 |1800 |9800 |3100
2023|12 |1800 |6800 |4100
Total

How can we convert all values of for example country=New Zealand then all values in 2023 after the existing month i.e if currently its march then(From april to dec)the amt should be changed to 0.10 as shown below

Year|Month| Bolivia|India| New Zealand

2021|1 |2800 |6800 |1100
2021|2 |3800 |6800 |2100
2021|3 |4800 |9800 |3100
2021|4 |5800 |8800 |4100
2021|5 |6800 |8800 |5100
2021|6 |7800 |8800 |6100
2021|7 |8800 |6800 |7100
2021|8 |9800 |4800 |8100
2021|9 |9800 |4800 |9100
2021|10 |6800 |3800 |1100
2021|11 |6800 |1800 |2100
2021|12 |5800 |2800 |6100
Total
2022|1 |1800 |6800 |2100
2022|2 |2800 |4800 |3100
2022|3 |3800 |3800 |6100
2022|4 |4800 |2800 |8100
2022|5 |5800 |1800 |9100
2022|6 |6800 |8800 |8100
2022|7 |7800 |9800 |7100
2022|8 |8800 |9800 |6100
2022|9 |9800 |5800 |5100
2022|10 |1800 |3800 |4100
2022|11 |1800 |1800 |3100
2022|12 |1800 |2800 |2100
Total
2023|1 |800 |8020 |9100
2023|2 |8000 |8003 |8100
2023|3 |3000 |8003 |7100
2023|4 |8090 |8005 |0.10
2023|5 |8008 |8060 |0.10
2023|6 |8003 |8080 |0.10
2023|7 |2800 |3800 |0.10
2023|8 |3800 |8200 |0.10
2023|9 |2800 |2800 |0.10
2023|10 |8100 |1800 |0.10
2023|11 |1800 |9800 |0.10
2023|12 |1800 |6800 |0.10
Total

2

Hi,

Can you try the following sample?

image
image857×1294 36.9 KB 

CurrentRow("Year").ToString.IsNumeric AndAlso New DateTime(CInt(CurrentRow("Year")),CInt(CurrentRow("Month")),1)>Now

Sample20230331-1aL.zip (3.4 KB)

Regards,

3

Thanks for sharing @Yoichi

Any other approach can be used?

4

Hi,

We can also use LINQ as the following.

image
image703×715 20.1 KB 

dt = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r("Year"),r("Month"),r("Bolivia"),r("India"), if(r("Year").ToString.IsNumeric AndAlso New DateTime(CInt(r("Year")),CInt(r("Month")),1)>Now,"0.10",r("New Zealand"))},False)).CopyToDataTable

Sample20230331-1aLv2.zip (5.4 KB)

Regards,