Advent Challenge #7

[ppr (Peter)]

Welcome to the 2020 Advent Challenge #7!

All users are welcome! (You can still join. You don’t need to participate in previous challenges)

Earn Stars to increase your chances to win a prize
More info here.

→ Rudolph: Hi Rudolph, how are you?

→ : I am fine, how are you?

→ : Well, you know there a lot of organization tasks to do like lists, creating reports and so on. As you entered into the digitalisation and automation business (Advent #6) maybe you can me.

→ : Yes, sure I will do. Currently the on the so I have to for some . Tell me.

→ : The are helping and do a lot of with the status of their tasks. It would not to lose the when it could be to some . Can we do it with the of ?

→ : What do you from the ?

→ : I do need ) an aggregated report and ) a filtered and sorted datatable.

→ : Oh, . I have to and to with . Let me your UiPath forum and the from there will you.

So give a help to Santa and show case on how such tasks are solved with UiPath.

*Please include in your reply which challenge you are solving - “Beginner” or “Advanced”

Beginners Advent Challenge #7:

• input: Data.csv.txt (305 Bytes)
• output: CSV File: ResultBeginner.csv
  • All rows belonging to a particular RegionCode are to compiled to:
  • Concat all Tasks (seperator: “,”) (like: Task1, Task2)
  • Sum up the efforts

To Do:

• Solve the task
• Upload evidence (screenshot/s) that you successfully obtained the results using UiPath.
• AND Upload your XAML/uiPathproject as ZIP in your reply

Advanced Advent Challenge #7:

• input: Data.csv.txt (305 Bytes)
• output: CSV File: ResultAdvanced.csv
  • All rows with task status “DONE” are filtered out
  • sort the remaining rows on:
    • First sort Criteria: Status within the order: NEW>WIP>PAUSED
    • Second sort Criteria: StartDate

To Do:

• Solve the task
• Upload evidence (screenshot/s) that you successfully obtained the results using UiPath.
• AND Upload your XAML/uiPathproject as ZIP in your reply

Deadline:

Submissions must be posted in this thread and will be accepted until 2020-12-21T22:59:00Z

Bonus points will be awarded for

• the first correct solution in its category
• the most minimalist correct solution

[Veena_Siva]

Beginner Solution
Hi Santa
Here are the steps in resolving the challenge

This is what is used for grouping, concatenating the task and finding the TotalEffort
(From p In sourceDT.AsEnumerable()
Group By x= New With { Key.a =p.Item(“RegionCode”)}
Into Grp = Group Select groupedDT.LoadDataRow (New Object() {grp(0)(0), String.Join(“,”,grp.select(Function(c) c(“Task”))),Grp.Sum(Function(c) CDbl(c(“Effort”)))},False)).CopyToDataTable()

Hurray!!! The csv is as below (“::” is used in separator for the print only)

Attached the project
AdventChallenge_7.zip (4.0 KB)

[rpavanguard]

Advanced VB.NET

Before:

After:

Log:

Flow (Hope Santa can read the Outline Style)

File:
Advent_2020_Challenge7_Advanced_VBNET.xaml (15.5 KB)

[jeevith (Jeevith Hegde, Ph.D.)]

Ho ho ho… Santa sure had some fun writing tasks in different regions of the world !

The approach here was to use standard UiPath activities and fix Santa’s issues.

1. Fix the file extension issue
2. Filtering out on Status = "DONE"
3. Clean up the date formats
4. Sort on Status and StartDate
5. Write output datatable to ResultAdvanced.csv

The output written to console and saved to ResultAdvanced.csv (that date formatting took time, Santa!)

Challenge was interesting because of the linked sort criteria. This solution is scalable, but I will be eagerly waiting to read the performance based solution from @StefanSchnell

Here is my submission to the advanced advent challenge # 7 (run Main.xaml): AdvancedAdventChallenge7.zip (5.8 KB)

[StefanSchnell]

Advent Challenge #7 Beginner

Santa had to think again about how best to solve this challenge. At first, he tried generic approaches in order to be able to react as flexibly as possible to structural changes. But he decided to use a specific approach, because it is easier to understand and read.

First, a data table is created with the different columns and their types. Important is here the column Effort and StartDate. The following steps reads the data and copies them into the table. Here is very important the conversion of the Effort to single and StartDate to DateTime type. One line of LINQ code contains a query that sorts, groups, selects and sums. Last but not least the result table is written to the output window.

//-Begin----------------------------------------------------------------

//-CSV to DataTable-----------------------------------------------------
 DataTable CSVTable = new DataTable();
 CSVTable.Columns.Add("RegionCode", typeof(string));
 CSVTable.Columns.Add("AdressID",   typeof(string));
 CSVTable.Columns.Add("Task",       typeof(string));
 CSVTable.Columns.Add("Effort",     typeof(Single));
 CSVTable.Columns.Add("Status",     typeof(string));
 CSVTable.Columns.Add("StartDate",  typeof(DateTime));

//-Read CSV file--------------------------------------------------------
StreamReader CSV = new StreamReader("Data.csv");

//-Write the data from the CSV file into the DataTable------------------
string Line = string.Empty;
int i = 0;
//-Read the lines and put them into the DataTable-----------------------
while((Line = CSV.ReadLine()) != null) {

  //-Jump over header line----------------------------------------------
  i += 1; if(i == 1) continue;
  string[] Row = Line.Split(';');

  DataRow CSVRow = CSVTable.NewRow();
  CSVRow["RegionCode"] = Row[0];
  CSVRow["AdressID"]   = Row[1];
  CSVRow["Task"]       = Row[2];
  //-Convert column Effort to data type Single--------------------------
  Single Effort = Single.Parse(Row[3], CultureInfo.InvariantCulture);
  CSVRow["Effort"]     = Effort;
  CSVRow["Status"]     = Row[4];
  //-Convert column StartDate to data type DateTime---------------------
  DateTime StartDate;
  DateTime.TryParseExact(Row[5], new string[]{ "MM/d/yyyy", "MM/dd/yyyy", "d.MM.yyyy", "dd.MM.yyyy"}, CultureInfo.InvariantCulture, DateTimeStyles.None, out StartDate);
  CSVRow["StartDate"]  = StartDate;
  CSVTable.Rows.Add(CSVRow);

}

//-Query data from DataTable--------------------------------------------
CSVTable = CSVTable.AsEnumerable()
  .OrderBy(o => o.Field<string>("RegionCode"))
  .GroupBy(g => g.Field<string>("RegionCode"))
  .Select(s => {
    var row = CSVTable.NewRow();
    row["RegionCode"] = s.Key;
    row["Task"] = String.Join(",",s.Select(z => z.Field<string>("Task")));
    row["Effort"] = s.Sum(r => r.Field<Single>("Effort"));
    return row;
  }).CopyToDataTable();

//-Output Result--------------------------------------------------------
string Output = string.Empty;
foreach(DataRow Row in CSVTable.Rows) {
  Output += Row[0] + ";" + Row[2] + ";" + Row[3] + Environment.NewLine;
}
Console.WriteLine(Output);

//-End------------------------------------------------------------------

Here the result.

Santa tried it in C# again and implements it in UiPath as Invoke Code activity.

Main.xaml (7.3 KB)

In a further step Santa will try this approach also with more activities. Santa looks at his notes and continues to think.

Hint: It is not necessary to convert the StartDate field into the right data type for this beginner challenge, but Santa thinks it is better to convert any column in the right format. So you can calculate with them correctly and sorting is also very simple, also for the advanced challenge.

Santa tried LINQPad to check other possibilities. Santa can use his code to define and read the data table. Only the query and the output are different, just to try it out.

This approach is also easy to read and Santa means that the clear view of the result is an advantage.

Also Santa tried an additional approach. He create the data table outside the Invoke Code activity and uses the Build Data Table activity.

A very comfortable way to define a data table he thinks. Especially because the correct data types can be defined here as well. He tried also to use the Read CSV activity, but without success. Therefore, Santa has left the reading of the data in the Invoke Code activity.

The Invoke Code activity has now one parameter…

… and the code is a little bit reduced.

//-Begin----------------------------------------------------------------

//-Read CSV file--------------------------------------------------------
StreamReader CSV = new StreamReader("Data.csv");

//-Write the data from the CSV file into the DataTable------------------
string Line = string.Empty;
int i = 0;
//-Read the lines and put them into the DataTable-----------------------
while((Line = CSV.ReadLine()) != null) {

  //-Jump over header line----------------------------------------------
  i += 1; if(i == 1) continue;
  string[] Row = Line.Split(';');

  DataRow CSVRow = CSVTable.NewRow();
  CSVRow["RegionCode"] = Row[0];
  CSVRow["AdressID"]   = Row[1];
  CSVRow["Task"]       = Row[2];
  //-Convert column Effort to data type Single--------------------------
  Single Effort = Single.Parse(Row[3], System.Globalization.CultureInfo.InvariantCulture);
  CSVRow["Effort"]     = Effort;
  CSVRow["Status"]     = Row[4];
  //-Convert column StartDate to data type DateTime---------------------
  DateTime StartDate;
  DateTime.TryParseExact(Row[5], new string[]{ "MM/d/yyyy", "MM/dd/yyyy", "d.MM.yyyy", "dd.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out StartDate);
  CSVRow["StartDate"]  = StartDate;
  CSVTable.Rows.Add(CSVRow);

}

DataTable tmp = CSVTable;

//-Query data from DataTable--------------------------------------------
CSVTable = tmp.AsEnumerable()
  .OrderBy(o => o.Field<string>("RegionCode"))
  .GroupBy(g => g.Field<string>("RegionCode"))
  .Select(s => {
    var row = tmp.NewRow();
    row["RegionCode"] = s.Key;
    row["Task"] = String.Join(",",s.Select(z => z.Field<string>("Task")));
    row["Effort"] = s.Sum(r => r.Field<Single>("Effort"));
    return row;
  }).CopyToDataTable();

//-End------------------------------------------------------------------

Hint: To use a reference or output parameter inside a query expression, it is necessary to assign an internal variable to it, in this example tmp.

Main.xaml (12.7 KB)

This example shows us very nicely how Invoke Code activities can be integrated into a workflow, Santa thinks. He learned a lot about LINQ.

[monsieurrahul (Rahul Prabhu)]

Santa did something new. Interested to know? Read

: Wow, its just me this time. I really can’t wait to build a workflow which can generate these reports for me.

Determined Santa reads the rules, opens UiPath and creates a blank process to solve the Beginners Challenge.

: Alright, next step is to read the input file and convert it into a DT.

: Alright, this was easy! Now, lets see, I have to filter each region code and process them altogether since I need the sum of efforts. Hmm.

: Sigh. This was a good experience.

:

: This is unexpected! What should I do now? I know, I should Google search this.

Santa searches for object reference error in uipath

He found a topic which lead to another one with solution (thanks to @ovi)

And guess what he did?

: Ho ho ho! This is awesome! Now, let me build the second workflow before I start celebrating…

Santa gulps a cup of hot chocolate and starts working on his building the Advanced Challenge.

: Filtering seems easy, let me do that first.

: Now comes the hard part! Sorting with date and status.

Santa works on this for several minutes and after about 40 minutes, he runs his second workflow.

: What? I got it right on the first go! Time to celebrate

: You just did a great job, Santa! You should become an RPA Developer.

: Yes, I’m willing to learn it after the holiday season!

And just like that Santa completes building his workflow and learns a lot doing it. We can see that, right? Look how happy he is!

Let us use his workflow to learn as well: UiPath-Advent-Day7.zip (8.9 KB)

Thanks for this @ppr, @Luiza, @Pablito, @Vibhor.Shrivastava, @loginerror, @Steven_McKeering, @AndersJensen and others. Appreciate your efforts!

Cheers,
Rahul

[StefanSchnell]

Advent Challenge #7 Advanced

Santa has nothing so much to do to modify his Beginners approach to use it also for Advanced.

Only two sequences need to be modified, the query and the output. In the query Santa needs an own custom order and a where clause to exclude the records with the DONE status. Santa is glad he made sure to convert the StartDate field in the Beginner challenge, so now he can just use it in the order clause. All fields are now displayed in the output.

//-Query data from DataTable--------------------------------------------
string[] customOrder = { "NEW", "WIP", "PAUSED" };
CSVTable = CSVTable.AsEnumerable()
  .OrderBy(o1 => Array.IndexOf(customOrder, o1.Field<string>("Status"))).ThenBy(o2 => o2.Field<DateTime>("StartDate"))
  .Where(w => w.Field<string>("Status") != "DONE")
  .CopyToDataTable();

//-Output Result--------------------------------------------------------
string Output = string.Empty;
foreach(DataRow Row in CSVTable.Rows) {
      Output += Row[0] + ";" + Row[1] + ";" + Row[2] + ";" + Row[3] + 
        ";" + Row[4] + ";" + Row[5] + Environment.NewLine;
}
Console.WriteLine(Output);

Here the result.

Main.xaml (7.3 KB)

Santa likes LINQ, powerful, easy to use and easy to read.

Santa asks his elves, why they didn’t bring this to his attention earlier. Sometimes it just takes a UiPath Advent challenge, the elves replied and smiled.

[abu.behlim (Abu Hurera Behlim)]

Beginner

Hello Santa ,

I’m back again and it was super fun in #AdvenChallenge6 to finding the escaped reindeers.

But now, you’re facing the new challenge again. This is damn tricky

Ok Santa , Can you hear me ? If you’re able to hear my voice then send me that text file now.

I have sent you the file Mr.Robot . Please check and kindly help me in to resolve this challenge.

Ok, I know the solution of this challenge because similar kind of challenge I had done before.

So, first I will convert the text file into CSV format and after that will do some Grouping, Sum up of Effort and Concatenate of the Task.

So here is the workflow of the challenge 7

2

With the help of LINQ expression we will solve this challenge Santa

That’s great. Then do it fast Mr. Robot .

Ok Santa , Let’s check the output result by running the code.
I’m using my secret key for running the code.

And here is the output

You’re genuis Mr.Robot

And nobody can this in a better way than you.

Thank you again for helping me to resolve this challenge again.

AdventChallenge_7_Beginner.zip (3.7 KB)

Advanced
Ooopsssss, the Advanced challenge is soo tricky

Ok, Let’s solve this challenge

So I will search the DONE Status and will remove it with the help of filter dataTable

Then we will Parse the StartDate Column in “dd/MM/yyyy” format for sorting.

Santa Let’s have a look on the workflow for the Advent Challenge 7 Advanced

2

Then we will match the Sorted Array Status {“NEW”,“WIP”,“PAUSED”} with the Filtered Data Table for Sorting the Status Column in this order NEW>WIP>PAUSED. with the help of Select Query

DT1.AsEnumerable().Where(Function(a) a(“Status”).ToString=GetStatus.ToString).CopyToDataTable

Santa : Mr.Robot I’m in love with your talent, Passion and programming Skills

Thank you Santa But this is not over yet

After sorting the Status column I will Append the Data into CSV file.

Here is the Workflow 3

Now the code is ready for launch

Santa don’t be in panic. I’m running the code and wait for the final output.

And here is the Final CSV output.

MR.Robot You have done it again

Thank you again for helping me Mr. Robot

AdventChallenge_7_Advance.zip (4.5 KB)

[seanrockvz13 (Sean Jerome Llanto)]

Ahoy there !
sent me a asking for help on both 1 and 2 of your

I am here to help solve both of your problems.

For Beginner:

I tried to make the solution as simple as possible

1. Read Text File
2. I generate a data table to convert the text file into a datatable using ; as a column delimiter
   3_ Get Distinct Regions by using dtinputData.AsEnumerable.Select(Function (r) r(0).toString.Trim).Distinct().ToList
3. Loop on each Distinct Regions
   Filter data table based on distinct Region
   Do a for each Loop on the filtered data table based on region
   Capture Task List and Value
4. After Coming out of both loops i have a string valrible
5. I generated another Data Table for output
6. Then created a ResultBeginner.csv file

Note: The output can be a notepad or even a writeline. I just decided to follow the requirements to put it in a CSV file

Im a good boy correct?

Please refer to the solution here for Beginner:

And it does not stop there!!!

Please refer to the solution on the Advanced Solution

1. First, assigned into an array of Strings the sort order
2. read the Data.csv.txt
3. Generate a data table to convert the text file into a datatable using ; as a column delimiter
4. Filter the data table to remove the status “Done”
5. Format date to follow the same structure (Using For each row while getting each date)

Oopps why Mari Condo please move away for now…

6. Loop into the Sort Order staring with New, WIP, Pending
   Filter Data table with the Sort Value
   Sort Data table result of filter by date
   Append Value to CSV

Finally,

here is the output

And of course you can refer to the code as well

Advent Challenge 7.zip (14.4 KB)

Voila!!

Bye for now!!!

[rpavanguard]

Is this forum thread open to sharing an opinion?

thanks!

[Maneesha_de_silva]

@rpavanguard
you can use this

Advent of UiPath 2020

What is the “Advent of UiPath 2020”? The Advent of UiPath is an advent calendar with RPA challenges for everyone in the UiPath Forum Community. Who should play? Anyone that is interested in learning UiPath and/or want to learn more UiPath . The calendar will touch all the important topics we face as RPA developers. When does the event run until? Until the 25th of December there will be challenges , which participants can try to solve to gain stars …

[rpavanguard]

@Maneesha_de_silva

you can use this

Done!

Advent of UiPath 2020 - News / Advent of UiPath 2020 - UiPath Community Forum

[Markus_Anding]

Beginners Challenge this time.

Take care out there!

Main.xaml (13.4 KB)

[Yoichi]

(Advanced Advent Challenge #7)
Dear Santa,

I’ll give you quick solution as the following.

Main expression is as follows:

dt.AsEnumerable.Where(Function(r) r("Status").ToString()<>"DONE").OrderBy(Function(r) Array.IndexOf(arrStatus, r("Status").ToString())).ThenBy(Function(r) DateTime.ParseExact(r("StartDate").ToString(),arrDtFormat,nothing,nothing)).CopyToDataTable

Note: arrStatus ={"NEW","WIP","PAUSED","DONE"} arrDtFormat={"MM/dd/yyyy","MM/d/yyyy","dd.MM.yyyy","d.MM.yyyy"}

AdventChallenge7.zip (3.4 KB)

Regards,

[abu.behlim (Abu Hurera Behlim)]

Hey @Yoichi,

I’m very impressed with your solution and you nailed it.

[stefano_negro]

Advanced Challenge

Dear Santa,

I understand that with Christmas approaching you’ll be very busy and things will start to stack up, so it’s better to keep everything in order!

So first we start to get the file into a DataTable and sort for StartDate

even if the dates are in different format we can use two ways to parse into the same format as output, in order to have a unique calendar!

After that we can filter for every different Status we want to keep, and merge the results as they come out

Finally, we have your data clean and tidy into a CSV, ready for you to hadle!

See you to next challenge!

AdvChallenge7.xaml (12.4 KB)

[stefano_negro]

Beginner Challenge

Dear Santa,

so you have another problem with sorting out data?

ok, this time let’s try to create a new table,starting from the initial csv, that contains the columns needed as output.
After this, we loop through the input and create one row for every region code

in the row we add every Task and the sum of the effort

so that the result will be stored as csv

Now you can manage your task easily!

AdvChallenge7.xaml (14.5 KB)

[Maneesha_de_silva]

Ho ho hoo…Mary Christmas

If you wish to watch my Santa Stories…

Episode #1 ==> Advent Challenge #1

Episode #2 ==> Advent Challenge #2 (Everyone welcome to join the event!)

Episode #3 ==> Advent Challenge #3

Episode #4 ==> Advent Challenge #4

Episode #5 ==> Advent Challenge #5

Episode #6 ==> Advent Challenge #6 - #24 by Maneesha_de_silva

Welcome to my Santa Story Episode #7 (Beginner & Advanced Challenge Inside)

Oh no…………. not again……………………

Santa was crying because of newly trained reindeers also escaped again, this time he came so far from CLAIRE there is no way of help from her. Santa was in distress. This time he has no idea what to do … he had only to option to take a call to Rudolph

→ hay Rudolph…
→ : hellow santa again ,really sorry I’m still with
→ ehhh, forget about the I’m in big trouble I need your help now,
ok… ok… tell me what happened ?
→ my reindeers are escaped again what should I do now…

Rudolph is thinking …

→ : Ok Santa I have one option, I can send my elephant, that only I have now,
: ohhhh elephant ???, If there is no option then send it at least be quick
Ok I will ready my send it to you, probably it will take 2 Days …
: oh …wait 2 Days ??? are you kidding me Rudolph ?, I don’t have much time to wait like that I need something like ASAP
no way Santa really sorry ,

ohhh Santa wait my flatbed scanner let me digitize my elephant send to you

Meanwhile Santanoooo not again…
It looked like it was his 975,846th attempt now…

: I don’t think that such a good idea
Let me try … Let me try…
Santa really knows that’s impossible but didn’t tell Rudolph because he doesn’t need to break Rudolph heart

But all the incidents looked at by Zeus, because he wanna give another return back to Santa (you may know what happened with Zeus in my Episode #1/ Episode #4 )

But this time Zeus sorrowful about Santa and Finally he decides foget about the fight between them and I should be able to help Santa because a lot of innocent kid dreams belongs on his hand

But… He was shy to directly help Santa because of the things on happened between them, Suddenly he saw about Rudolph trying to digitize an elephant to send the Santa

Zeus got the idea of how to help him

he activate his ultimate magical power and send it into Rudolph’s small flatbed scanner

=======Miracle happened =================
Suddenly the scanner transformed into Digital Teloprt Machine

yahooooooooooooooo yhooooooooo i made it i made it!!! , Rudolph was very happy and dancing around of it.
And he starts the Digitize the elephant and Sends it to Santa’s Location

Elephant appiers in to Santa’s Location

Ho ho ho… awsome thanx Rudolph
Lets go…
ohh… wait my machical meals , how can elephant fly without it
But this elephant refuse to take it by directly , so santa had to give it inside Bananas

Old Is Gold, But alone it’s difficult, So some guys helped Santa to climb on to the elephant

Ho ho, Ho… I’m ready, let’s go ma… big boy…

Then they takeoff into the sky

When flying he saw that Uipath Cloud…
then suddenly he realizes that he had a remaining task to do suddenly he checkout Uipath Forum and he saw that a guy @ppr is like a very active robot master and he ask him to help with his problem

@ppr: Ok Santa I have plenty of nice friends around me yo will end up with various options to manage your questions
PPR post a Post in Advent Challenge #7

HOHOHO… such awesome answers I better give some gift for them
What do you think …

Main Biginner.xaml (16.5 KB)

Advanced.xaml (17.3 KB)

PS: Anyway Santa realizes that the elephant is not enough to trace because of its size the landing makes many sounds and Santa got caught by too few kids while he entering the house.
And Santa now thinking about his retired reindeer to CALL OF DUTY

TO BE CONTINUED…FINAL Episode -25th December

[Anna_Maziarz (Anna Maziarz - Office Samurai)]

Beginner
Hello Santa !
Below you can find my solution for Advent Challange #7

Beginner.xaml (13.4 KB)