Assigns status to related activities

[wsinten (KANZ)]

Hello all,

I need help. I want to give status to students who are doing activities in and out of the room to monitor their position like this.

Do I have to use a formula in excel or is there an activity that can do that? Please guide me. thank you

[Yoichi]

Hi,

If my understanding is correct, the following will work. Can you try this?

dt = dt.AsEnumerable.GroupBy(Function(r) r("Name").ToString).SelectMany(Function(g) g.Select(Function(r) dt.Clone.LoadDataRow({r(0),r(1),r(2),if(g.Last().Item("Activity").ToString="OUT","OUTDOOR","")},False))).OrderBy(Function(r) r("ID").ToString).CopyToDataTable

Sample20230313-3L.zip (15.6 KB)

Regards,

[Anil_G (Anil Gorthi)]

@wsinten

May I know on what basis you are setting outdoor as I see outdoor is set when I see In as well in activity…

If it is only using activity to decise then use a write cell activity and use a formula in the first cell and then use autofill range activity to fill the formula in all cells till the bottom

Write cell with cell as "D2" and value as below

"=IF(C2=""OUT"",""OUTDOOR",""INDOOR"")"

Then use autofill range as next activity with range as "D2" then it will fill the formula till the data is present

Hope this helps

Cheers

[wsinten (KANZ)]

Hi @Yoichi
thanks for answering. I will try the code you sent first

[wsinten (KANZ)]

hi @Yoichi,

it worked. But when I implement it in my project, it still fails. I want to put “position” in column “I”.

Is there still something wrong with the code that I wrote?

Please correct me sir.

dt.AsEnumerable.GroupBy(Function(r) r("KETERANGAN").ToString).SelectMany(Function(g) g.Select(Function(r) dt.Clone.LoadDataRow({r(3),r(4),r(5),If(g.Last().Item("D/K").ToString="OUT","OUTDOOR","")},False))).OrderBy(Function(r) r("REFF").ToString).CopyToDataTable

[Yoichi]

Hi,

Can you share your worksheet as a file? It’s no problem if dummy data and has same column.

Regards,

[wsinten (KANZ)]

Hi, this is my workbook
TEST1.zip (9.0 KB)

[Yoichi]

HI,

Can you try to modify it as the following?

dt = dt.AsEnumerable.GroupBy(Function(r) r("NAME").ToString).SelectMany(Function(g) g.Select(Function(r) dt.Clone.LoadDataRow(r.ItemArray.Take(8).Concat({if(g.Last().Item("ACTIVITY").ToString="OUT","OUTDOOR","")}).ToArray,False))).OrderBy(Function(r) r("ID").ToString).CopyToDataTable

Then Write it from A1

Regards,

[wsinten (KANZ)]

Hi, In fact nothing happens when I use that command in my workbook. does that command work in your workbook sir?

[Yoichi]

HI,

I think there is no “OUTDOOR” result in your sample as it is,because all end with “IN” as the following.

Can you try to modify some Activity to have result “OUTDOOR”?

Regards,

[wsinten (KANZ)]

thank you very much for helping me sir. I’ve made changes, but it still doesn’t work as shown in the picture

[Yoichi]

Hi,

I think they are still IN as the following.

Regards,

[wsinten (KANZ)]

But lines 1,2,3,4,8,9 show no status when james and peter are outside the master’s room sir

[Yoichi]

Hi,

Can you elaborate your condition? I think your expected result in the first post shows it should be final state in Position Column, as the following.

Regards,

[wsinten (KANZ)]

hello @Yoichi,

It turns out that the code you provided is working. and this error is on me. thank you very much sir. may you always be healthy​:pray: