Advent Challenge #8

Amazing solutions @stefano_negro and @jeevith. I was thinking of to create result data table and iterate through tasks and execution to calculate first two values, third one i had to try ,nothing came in mind. Though holidays started little early for me i can’t stop checking this forum.Wish i had my laptop😌
Merry Xmas all!!!

4 Likes

Beginner
Here is my solution for Beginner challenge.
It was not an easy challenge, but I learned a few new things :ok_hand:
First I read the data, create datatable for my result and add unique taskID to that datatable and also to list.

Then for each unique Task I checked conditions using mainly expression : dt.AsEnumerable().Any(Function(x) x() = ValueSearched)

I checked the last condition in two steps. The frist step checks if there are any tasks that are not in the Executions, if yes assign False. The second step checks the condition by iterating through each row in Executions datatable.

The result :

result
Advent challenge 8.zip (182.0 KB)

Thank you for organizing this Advent Challenge !! It was a great idea and every challenge taught me something new.

I wish all community merry Chritmas and Happy New Year !

6 Likes

Hi ,
Here is my solution for Beginner and Advanced challenge.

Challenge8.xaml (27.8 KB) DataAdvent#8.xlsx (12.1 KB)

BEGINNER SOLUTION :

Check for isDefinedTask Rule:

This rule has been verified using Join of 2 data tables Execution and Task by checking same Task ID . Then Remove the List of Task ID from previous step from Execution Datatable. Update these IDS as True

Check for TaskIsStarted Rule

This rule has been verified using Join of 2 data tables Execution and Task by checking same Task ID .Update these IDS as True

Check for hasValidRCExcuted Rule:
This rule has been verified using Join of 2 data tables Execution and Task by checking same Task ID and also checks contains of the Region Code .Update these IDS as True

ADVANCED SOLUTION

Check for CompletedAllValidRCs Rule
Filter the Execution List with result ok. This rule has been verified using Join of 2 data tables Execution and Task by checking same Task ID and also checks not contains of the Region Code .Then Join above result with Task to check for the Region code .Update these IDS as True.

Output :
image
I tried the solution using Linq queries.

3 Likes

For Beginner
Hey Santa
here the solution for your problemTask.xaml (27.4 KB) Resiult.xlsx (9.4 KB)

3 Likes

Advent Challenge #8 Beginners

Santa muses about many things, especially using Excel files without an Excel installed. Santa reads a tip about EPPlus, a library to use Excel files without Excel, and tried it in this challenge. Santa remembers Challenge #3, he is still a poor man and still wishes an Office Suite. But he doesn’t have one yet, so he uses EPPlus to read the Excel files.

Furthermore, Santa has decided to use SQL, a query language widely used in business. He has tried LINQ and has his problems with it. That’s why Santa uses SQLite in this challenge.

Why SQLite an elf asked :elf:. Santa answers, this library implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. It is the most used database engine in the world and its built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. It can only be a benefit to use these in the context of UiPath. What you have learned here you can use everywhere again.

Santa downloads from SQLite.org the precompiled binaries for .NET, here the statically linked Binaries for 32-bit Windows. Santa builds a NuGet package, to import it via UiPath Package Manager.

Here the nuspec file to create the NuGet package:

<?xml version="1.0"?>
<package >
  <metadata>
    <id>SQLite</id>
    <version>1.0.113.0</version>
    <authors>Public Domain</authors>
    <description>SQLite</description>
    <tags>sqlite sql database</tags>
  </metadata>
  <files>
    <file src="System.Data.SQLite.dll" target="lib\net46"></file>
  </files>
</package>

After the installation of the additional packages Santas environment looks like this:

image

EPPlus and SQLite are imported successfully. Now I am ready to go, Santa thinks. :santa:

Now Santa code in C# the following sequence:

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

try {

  string sql = string.Empty;
  SQLiteCommand cmd;

  //-Create database----------------------------------------------------
  SQLiteConnection.CreateFile("DataAdvent.db");
  SQLiteConnection con;
  con = new SQLiteConnection("Data Source=DataAdvent.db;Version=3;");
  con.Open();

  //-Create Tasks table-------------------------------------------------
  sql = "CREATE TABLE Tasks (Task TEXT, RegionCode TEXT)";
  cmd = new SQLiteCommand(sql, con);
  cmd.ExecuteNonQuery();

  //-Create Executions table--------------------------------------------
  cmd.CommandText = "CREATE TABLE Executions (Task TEXT, RegionCode TEXT, Attempt INTEGER, Result TEXT, Comment TEXT)";
  cmd.ExecuteNonQuery();

  //-Create Results table-----------------------------------------------
  cmd.CommandText = "CREATE TABLE Results (Task TEXT, eTask TEXT, tTask TEXT, isDefinedTask BOOLEAN, TaskIsStarted BOOLEAN, hasValidRCExecuted BOOLEAN)";
  cmd.ExecuteNonQuery();

  //-Open Excel file----------------------------------------------------
  ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

  FileInfo fi = new FileInfo("DataAdvent.xlsx");
  ExcelPackage Excel = new ExcelPackage(fi);

  //-Copy data from Tasks worksheet to Tasks table----------------------
  ExcelWorksheet ExcelTasks = Excel.Workbook.Worksheets["Tasks"];
  for(int row = 2; row <= ExcelTasks.Dimension.End.Row; row++) {
    cmd.CommandText = "INSERT INTO Tasks (Task, RegionCode) VALUES ('" + ExcelTasks.Cells[row, 1].Value + "', '" + ExcelTasks.Cells[row, 2].Value + "');";
    cmd.ExecuteNonQuery();
  }
  ExcelTasks.Dispose();

  //-Copy data from Executions worksheet to Executions table------------
  ExcelWorksheet ExcelExecutions = Excel.Workbook.Worksheets["Executions"];
  for(int row = 2; row <= ExcelExecutions.Dimension.End.Row; row++) {
    cmd.CommandText = "INSERT INTO Executions (Task, RegionCode, Attempt, Result, Comment) VALUES ('" + 
    ExcelExecutions.Cells[row, 1].Value + "', '" + 
    ExcelExecutions.Cells[row, 2].Value + "', " + 
    ExcelExecutions.Cells[row, 3].Value + ", '" + 
    ExcelExecutions.Cells[row, 4].Value + "', '" + 
    ExcelExecutions.Cells[row, 5].Value + "');";
    cmd.ExecuteNonQuery();
  }
  ExcelExecutions.Dispose();

  Excel.Dispose();

  //-Insert all available Tasks into Results table----------------------
  cmd.CommandText = "INSERT INTO Results (Task) SELECT Executions.Task FROM Executions UNION SELECT Tasks.Task FROM Tasks";
  cmd.ExecuteNonQuery();

  //-Update Executions and Tasks Task column in Results table-----------
  cmd.CommandText = "UPDATE Results SET eTask = (SELECT Task FROM Executions WHERE Executions.Task = Results.Task), tTask = (SELECT Task FROM Tasks WHERE Tasks.Task = Results.Task)";
  cmd.ExecuteNonQuery();

  //-Update isDefinedTask in Results table------------------------------
  //-
  //- If a task is present in eTask but not in tTask
  //- then isDefinedTask is false, otherwise true
  //-
  //--------------------------------------------------------------------
  cmd.CommandText = "UPDATE Results SET isDefinedTask = CASE WHEN (eTask IS NOT NULL AND tTask IS NULL) THEN false ELSE true END";
  cmd.ExecuteNonQuery();

  //-Update TaskIsStarted in Results table------------------------------
  //-
  //- If a task is defined in tTask and also present in eTask
  //- then TaskIsStarted is true, otherwise false
  //-
  //--------------------------------------------------------------------
  cmd.CommandText = "UPDATE Results SET TaskIsStarted = CASE WHEN (tTask IS NOT NULL AND eTask IS NOT NULL) THEN true ELSE false END";
  cmd.ExecuteNonQuery();

  //-Update hasValidRCExecuted in Results table-------------------------
  //-
  //- If a Task in Execution is executed,
  //- in a region which is not defined in Tasks for this Task ID
  //- then hasValidRCExecuted is false, otherwise true.
  //-
  //--------------------------------------------------------------------

  //-Create Tasks mirror table------------------------------------------
  cmd.CommandText = "CREATE TABLE mirrorTasks (Task TEXT, RegionCode TEXT);";
  cmd.ExecuteNonQuery();

  //-Split RegionCode with multiple entries-----------------------------
  cmd.CommandText = "INSERT INTO mirrorTasks (Task, RegionCode) WITH split(Task, RegionCode, str) AS (SELECT Task, '', RegionCode||',' FROM Tasks UNION ALL SELECT Task, substr(str, 0, instr(str, ',')), substr(str, instr(str, ',') + 1) FROM split WHERE trim(str) != '') SELECT Task, RegionCode FROM split ORDER BY Task, RegionCode;";
  cmd.ExecuteNonQuery();

  //-Delete rows with empty RegionCode----------------------------------
  cmd.CommandText = "DELETE FROM mirrorTasks WHERE RegionCode IS NULL OR trim(RegionCode) = '';";
  cmd.ExecuteNonQuery();

  cmd.CommandText = "UPDATE Results SET hasValidRCExecuted = CASE WHEN (Task IN (SELECT e.Task FROM Executions AS e LEFT JOIN mirrorTasks AS t ON e.Task = t.Task AND e.RegionCode = t.RegionCode WHERE t.RegionCode IS NULL GROUP BY e.Task HAVING SUM(e.Attempt) > 0)) THEN false ELSE true END;";
  cmd.ExecuteNonQuery();

  //-Update hasValidRCExecuted in Results table-------------------------
  //-
  //- If the task was never executed
  //- then hasValidRCExecuted is false.
  //-
  //--------------------------------------------------------------------
  cmd.CommandText = "UPDATE Results SET hasValidRCExecuted = false WHERE Task NOT IN (SELECT Task FROM Executions GROUP BY Task HAVING SUM(Attempt) > 0);";
  cmd.ExecuteNonQuery();

  //-Delete Tasks mirror table------------------------------------------
  cmd.CommandText = "DROP TABLE mirrorTasks;";
  cmd.ExecuteNonQuery();

  //-Output of the Results table----------------------------------------
  cmd.CommandText = "SELECT Task, isDefinedTask, TaskIsStarted, hasValidRCExecuted FROM Results ORDER BY Task;";
  SQLiteDataReader Result = cmd.ExecuteReader();
  Console.WriteLine("TaskID\tisDefinedTask\tTaskIsStarted\thasValidRCExcuted");
  while(Result.Read()) {
    Console.WriteLine(
      Result.GetString(0) + "\t" +
      Result.GetBoolean(1).ToString() + "\t\t" +
      Result.GetBoolean(2).ToString() + "\t\t" +
      Result.GetBoolean(3).ToString()
    );
  }

  con.Close();

} catch(Exception ex) {
  Console.WriteLine(ex.Message);
}

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

The code is well documented, so there is no need for further explanation, Santa hopes.

With the code Santa gets this result:

image

Main.xaml (13.5 KB)

As far as Santa can see, it looks good.

Santa finds the direct use of a local SQL engine very exciting. Santa has worked in Walldorf for some time and sees the use of SQL as an interesting way in the context of UiPath.

Did you know that SQL is not called Structured Query Language? No, it is called Santa’s Query Language. Santa gave this idea away to IBM in the 70s. Unfortunately he did not patent the name.

7 Likes

Indeed it was a tough challenge… But happy got passed it!

Dear Santa! Here is the Beginner Challenge for Advent Challenge 8

image

Hopefully i can solve the advanced advent challenge tomorrow! Its 3am here and still have to work tomorrow.

Not able to create a story line! :smiley: :smiley: :smiley:

Merry Christmas to everyone!!

image

Oops before i forget, this is the solution to my beginner Advent Challenge

Advent Challenge 8.zip (15.5 KB)

Regards,

Sean :slight_smile:

4 Likes

Beginner

Hello Santa,

This time we will call Aliens :alien: :alien: :alien: :alien: to solve this challenge because this challange is not an easy :joy: :joy: :joy:

download

Santa :santa: :Mr. Alien read the above challange rules and solve this problem

Yeah Yeah, I know all the rules for this challenge santa :santa: . Give me the excel file.

_110424087_gettyimages-1130166565

I have sent the file on your Email ID :email:

Ok Santa :santa: I got the file.

So here is the workflow for this challenge. :alien:

  1. Check for isDefinedTask Rule:

Loop over the Tasks Sheet and check the TASKS name with the BuilDaTable data row for getting the DefinedTask values.


  1. Check for TaskIsStarted Rule

LINQ expression used for matching the TASK column with Task column for getting the matched Task number and then convert it into Array of String and match the string Array list (getArray) with BuildaData row through the For each Iteration for getting the TaskIsStarted values in TRUE and FALSE

  1. Check for hasValidRCExcuted Rule:

First check the whether the Task no in Tasks and Execution sheet is match with the BuildTable column (TaskID) or not.

Then match the row(1) values of both the sheets with RegionCode value and get the hasValidRCExecuted Rule in TRUE and FALSE form.

Let’s run the workflow and see the final output.

and here is the output:

Excel_Output

Thank you Mr.Alien :star_struck: :star_struck: :star_struck: :heart_eyes: :heart_eyes: :heart_eyes: for helping me.

Can I take a picture with you Mr.Alien :alien:

Why not santa :santa: :smiling_face_with_three_hearts: :smiling_face_with_three_hearts: Ok wait, Let me turn on my UEFO Fleverybodyash :crazy_face: :crazy_face:
download (1)

1… 2 … 3

And CLICK :camera_flash:

Merry Christmas :christmas_tree: :christmas_tree: to all & my UiPATH Family and Thank you UiPath for this amazing challenges :heart:

AdventChallenge_8_Beginner.zip (18.3 KB)

Advanced

  1. Check for CompletedAllValidRCs Rule

First check the whether the Task no in Tasks and Execution sheet is match with the BuildTable column (TaskID) or not.

Then match the row(1) values of both the sheets with RegionCode value , if the result is TRUE then check the Executions Task RESULT is “OK” or not. If condition matched then CompletedAllValidRCs will be TRUE otherwise it will be FALSE.

And here is the Advanced Output.

Advent_Advanced_8_Output

AdventChallenge_8_Advanced.zip (19.6 KB)

Thank you @dianamorgan @ppr @Luiza @Pablito @loginerror @Steven_McKeering @AndersJensen for this amazing challenge and I really enjoyed this Advent journey.
Lots of love to everybody.

6 Likes

:santa:: I really need to solve these problems, its T-1 day until Christmas. What can I do? I’m so confused.

istockphoto-479381278-612x612

:santa:: I know, I’ll text someone on forum, they will help me out while I work on getting the presents ready.

Luckily, Santa chooses me and sends me a message on the forum, here’s a preview of the message:

Hey Rahul, can you help me solve these using UiPath? I really need someone who can help me out with this: Advent Challenge #8

:bearded_person:t4:: Sure Santa, I can’t believe you reached out to me. I’ll be happy to help you with your tasks.

So, I decided to help Santa with this logic.

After building a dynamic workflow, I sent the same and explained the logic to Santa, which you can see below.

Advent Challenge #8, Beginner:

Workflow Screenshot:

Output File Screenshot:

image

Approach Used:

Main condition used is to check if the task listed in the executions sheet is available in the task sheet’s Task column and based on that perform manipulations to the build DT.

Advent Challenge #8, Advanced:

Workflow Screenshot:

Output File Screenshot:

image

Approach Used:

For the CompletedAllValidRCs, filter each distinct task from the executions sheet and add it to a list, similarly filter the RegionCode from the task sheet for the distinct task and add it to a list. If all the items in the task list are available in the other list then set the flag to true and if false, break as the end result is going to be false.

Finally performed a clean up to make things look better (just handling empty rows and sorting the DT)

And just like that I was able to complete this workflow.

Dear Santa, thanks for reaching out! Here’s the workflow for your future reference: UiPath-Advent-Day8.zip (36.3 KB)

Santa replies back:

This is great, thanks Rahul! I’ll give you an extra gift! :wink:

And our Santa uses this workflow to run his processes and starts delivering gifts. I mean it, look how happy he is.


By the way, sorry for the late reply guys, I was caught up at work. Had to squeeze this into my schedule.

Special thanks to @dianamorgan, @ppr, @Luiza, @Pablito, @loginerror, @Steven_McKeering, and @AndersJensen for coming up with creative problems that all of us enjoyed solving. Appreciate your efforts!

With this, I wish you all a Merry Christmas :christmas_tree::star2: and a Happy New Year :partying_face:

Stay safe! :mask:

Cheers,
Rahul

5 Likes

Kudos to my fellow problem solvers @StefanSchnell, @abu.behlim, @jeevith, @Maneesha_de_silva, @prashanthig and @Markus_Anding for solving all the problems and posting very creative solutions :clap:t4:

6 Likes

It was an amazing learning experience for me @monsieurrahul and I have learned alot throughout the Advent Challenge Event​:relieved::+1:.

Wish you a Merry Christmas​:christmas_tree::santa::bell::snowflake: and Happy New year to all my fellow :heart::heart::heart::heart:

3 Likes

Advent Challenge #8 Advanced

Santa modifies to his beginners challenge only in the definition of the Result table and the additional conditions. Here he adds the field CompletedAllValidRCs.

//-Create Results table-----------------------------------------------
cmd.CommandText = "CREATE TABLE Results (Task TEXT, eTask TEXT, tTask TEXT, isDefinedTask BOOLEAN, TaskIsStarted BOOLEAN, hasValidRCExecuted BOOLEAN, CompletedAllValidRCs BOOLEAN)";
cmd.ExecuteNonQuery();

Also in the output he adds the additional field.

//-Output of the Results table----------------------------------------
cmd.CommandText = "SELECT Task, isDefinedTask, TaskIsStarted, hasValidRCExecuted, CompletedAllValidRCs FROM Results ORDER BY Task;";
SQLiteDataReader Result = cmd.ExecuteReader();
Console.WriteLine("TaskID\tisDefinedTask\tTaskIsStarted\thasValidRCExcuted\tCompletedAllValidRCs");
while(Result.Read()) {
  Console.WriteLine(
    Result.GetString(0) + "\t" +
    Result.GetBoolean(1).ToString() + "\t\t" +
    Result.GetBoolean(2).ToString() + "\t\t" +
    Result.GetBoolean(3).ToString() + "\t\t\t" +
    Result.GetBoolean(4).ToString()
  );
}

Also he adds the new conditions to set the CompletedAllValidRCs.

//-Update CompletedAllValidRCs in Results table-------------------------
//-
//- The executions of task in Execution with the status “OK” are
//- relevant.
//- If a task is executed for all RegionCodes defined for this task
//- in the Task data
//- then CompletedAllValidRCs is true, otherwise false.
//-
//----------------------------------------------------------------------
cmd.CommandText = "UPDATE Results SET CompletedAllValidRCs = CASE WHEN ( Task = ( SELECT Task FROM ( SELECT Task, Task || RegionCode AS TaskRegionCode FROM Tasks WHERE TaskRegionCode = ( SELECT Task || RegionCode AS TaskRegionCode FROM ( SELECT Task, group_concat(RegionCode, ',') AS RegionCode FROM ( SELECT Task, RegionCode, Result FROM Executions WHERE Result = 'OK' ORDER BY Task, RegionCode ) GROUP BY Task ) ) ) ) ) THEN true ELSE false END;";
cmd.ExecuteNonQuery();

//----------------------------------------------------------------------
//-
//- If the task was executed on not defined RegionCodes
//- it will not count and not change the CompletedAllValidRCs result.
//-
//----------------------------------------------------------------------

In Santas opinion is SQL a great approach. Here a well formed example from the editor. Read it from inside to outside. It starts with the SELECT from Executions with the status ‘OK’. Then, to provide a comparability to Tasks, Santa concatenate all RegionCodes in one field and sets the CompleteAllValidRCs. In this example are all false.

image

The last condition gives Santa a headache.

The condition is not unique in Santas opinion. Santa assumes it means - if the task was executed on not defined RegionCodes it will not count and not change the CompletedAllValidRCs Check result.

Santa gets this result, after the third try. Sometimes too much thinking is not good, Santa thinks.

image

Main.xaml (15.9 KB)

The integration of the additional code sequences was very easy, as you can see.

Now Santa says goodbye to the UiPath Advent Challenge 2020, he still has enough to do in the next few days.

Santa says sincere thanks to @AndersJensen, @Steven_McKeering, Maciej Kuźmicz (aka @loginerror), Paweł Woźniak (aka @Pablito), Luiza Draghicean (aka @Luiza), Peter Preuß (aka @ppr) and @dianamorgan. That was really a challenge and Santa learned a lot.

5 Likes

(Beginners & Advanced Advent Challenge #8)
Dear Santa,

I’ll give you the following solution.

First of all, get TaskID list using Union method.

Next, There are 4 LINQ expression for each rule as the following. They return Dictionary<String, Boolean> which key is TaskID. They are a little bit complicated, but work.

dicts("isDefinedTask") = listAllTaskNumber.ToDictionary(Function(x) x, Function(x) dtTasks.AsEnumerable. _
Any(Function(r) r("Task").ToString=x) _
)

dicts("TaskIsStarted") = listAllTaskNumber.ToDictionary(Function(x) x, _
Function(x) dtTasks.AsEnumerable.Any(Function(r) r("Task").ToString=x) _
	AndAlso  dtExecutions.AsEnumerable.Any(Function(r) r("TASK").ToString=x) _
)

dicts("hasValidRCExcuted")=listAllTaskNumber.ToDictionary(Function(x) x, _
 Function(x) dtExecutions.AsEnumerable.Any( _
	 Function(r) r("TASK").ToString()=x _
		 AndAlso (dtTasks.AsEnumerable.Any(Function(rt) rt("Task").ToString()=x) _
		 AndAlso (dtTasks.AsEnumerable.Single(Function(rt) rt("Task").ToString()=x)("RegionCode").ToString.Contains(r("RegionCode").ToString)) _
    ) _
     ) _
)

dicts("CompletedAllValidRCs")=listAllTaskNumber.ToDictionary(Function(x) x, _
Function(x) dtTasks.AsEnumerable.Any(Function(r) r("Task").ToString()=x _
	AndAlso (dtTasks.AsEnumerable.Single(Function(rt) rt("Task").ToString()=x)("RegionCode").ToString.Split({","c}).All( _
	    Function(y) dtExecutions.AsEnumerable.Any( _
			Function(z) z("TASK").ToString=x _
			AndAlso z("REGIONCODE").ToString=y _
			AndAlso z("RESULT").ToString="OK") _
			) _
		) _
    ) _
) _

Finally, concatenate these dictionary value as string, then convert DataTable using Generate DataTable activity.

AdventChallenge8.zip (17.6 KB)

Thank you for organizing great challenge: @AndersJensen @Steven_McKeering @loginerror @Pablito @Luiza @ppr @dianamorgan

Merry Christmas to all!

Regards,

Yoichi

5 Likes

Hello @Yoichi, this is great :astonished: You have to make a community post on writing LINQ for beginners.

3 Likes

I think @Yoichi is Robot :grin:… He nailed it again with his LINQ amazing skills. :heart:

2 Likes

Hi guys! @abu.behlim and @monsieurrahul I have made a tutorial for the Advent Challenge #7 with LINQ here: UiPath Advent Challenge 2020 #7 | Filtering, LINQ, Invoke custom code | UiPath Tutorial - YouTube. I will have more content like this, so do the goodies with Subscribe and Like :slight_smile:

While I resolved the #8 challenge already I am posting the solution here and I will publish the video explained after Christmas

Advanced solution:

I think this is the shortest way to resolve this challenge :slight_smile:

LINQ:
var isDefinedTaskQuery = from executionTask in executions.AsEnumerable().Select(li => li.Field(“TASK”))
select new
{
Task = executionTask,
IsDefined = tasks.AsEnumerable().Select(li => li.Field(“Task”)).Contains(executionTask)
};

var taskIsStartedQuery = from taskRow in tasks.AsEnumerable() 
	                                     let task = taskRow.Field<string>("Task")
										select new 
										{
											Task = task,
											IsStarted = executions.AsEnumerable().Select(li => li.Field<string>("TASK")).Contains(task)
										};

var hasValidRCExcutedQuery = from executionRow in executions.AsEnumerable()
	                                               let task = executionRow.Field<string>("TASK")
											       let definedRegionCodes = tasks.AsEnumerable()
																									.Where(li => li.Field<string>("Task") == task)
																									.SelectMany(li => li.Field<string>("RegionCode").Split(new Char[] { ',' }))
	                                              select new 
					 				         	{
											         Task = task,
												  	 HasValidRCExcuted = definedRegionCodes.Contains(executionRow.Field<string>("REGIONCODE"))
												};
var completedAllValidRCsQuery = from taskRow in tasks.AsEnumerable()
			                                            let task = taskRow.Field<string>("Task")
			                                            let regionCodes = taskRow.Field<string>("RegionCode").Split(new char[] { ',' })
			                                            let completed = from executionRow in executions.AsEnumerable()
					                                                              where executionRow.Field<string>("RESULT") == "OK" && executionRow.Field<string>("TASK") == task
					                                                              select executionRow.Field<string>("REGIONCODE")
			                                            select new
			                                            {
			                                                Task = task,
			                                                CompletedAllValidRCs = regionCodes.All(li => completed.Contains(li))
			                                            };
												  
var query = from task in tasks.AsEnumerable().Select(li => li.Field<string>("Task")).Union(executions.AsEnumerable().Select(li => li.Field<string>("TASK"))).Distinct()
			       orderby task
				   select new 
				   {
					     Task = task,
					     IsDefinedTask = isDefinedTaskQuery.Any(li => li.Task == task && li.IsDefined),
					     TaskIsStarted = taskIsStartedQuery.Any(li => li.Task == task && li.IsStarted),
					     ValidRCExecuted = hasValidRCExcutedQuery.Any(li => li.Task == task && li.HasValidRCExcuted),
					     HasValidRCExcuted = completedAllValidRCsQuery.Any(li => li.Task == task && li.CompletedAllValidRCs)
				   };
				   
foreach (var task in query)
	resultDataTable.Rows.Add(new Object[] { task.Task, task.IsDefinedTask, task.TaskIsStarted, task.ValidRCExecuted, task.HasValidRCExcuted });

This could be done in fewer lines but It would been way hard to understand! Cheers!

4 Likes

A few minutes to go till Christmas! Since Rudolf was not able to help Santa

Another four legged creature faced the challenge and volunteered!

image

Yes! This is me Donkey!

And this is my response to advent challenge 8 - Advanced in continuation of the beginner challenge.

From the beginner challenge, i just added a few if conditions to check the following:

1st check is to check if all executed belong to the task list.
If not, then isCompletedAddValidRCs = False
2nd Check is to check the count of executed task against all of the regions available,
if match is equal, we need to loop to check all regions executed if belongs to the regions of the task.
If All Match, then isCompletedAddValidRCs = True
If even 1 is not matching, then isCompletedAddValidRCs = False

So, there is also a check to see if count of Ok Task is not equal to the regions in the task list, then automaticall it will be false. Because all regions MUST be ran.
Region Code executed belongs to the regions listed in the task (if Not there, the RC is false)


Attached is my solution for the Advanced Advent Challenge

Advent Challenge 8 - Advanced Solution.zip (19.3 KB)

And this is the result :slight_smile:
image
image

Thank you to all the moderators of the Advent Challenge, this allowed us to practice our UiPath skills and learn more things. This also allowed to to create different types of solutions accordingly.

@Steven_McKeering , @AndersJensen, @dianamorgan, @loginerror ,@ppr, @Luiza, @Pablito

Thank you for your time in creating the challenge as well as checking our solutions.

Merry Christmas to you and your family.

4 Likes

Ho ho hoo…Mary Christmas
:evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree: :evergreen_tree:

If you wish to watch my Santa Stories… :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down:

Episode #1 ==> :snowman_with_snow: Advent Challenge #1

Episode #2 ==>:santa: 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

Episode #7 ==> Advent Challenge #7 - #21 by Maneesha_de_silva

:star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck: :star_struck:

Welcome to my Santa Story Episode #8 (Beginner Challenge Inside)

:point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right: :point_right:

Anyway as the reason for (my Episode #7) anta realizes that the elephant,
And Santa now thinking about his retired reindeer to CALL OF DUTY

Meanwhile on that :police_car::policewoman::policeman: :door: the :elephant: :trumpet: :loud_sound: :loud_sound: :loud_sound: the :houses: :phone: :oncoming_police_car: :speaking_head: :bomb: :boom: about :elephant: Now :oncoming_police_car:<< this is last :bangbang: :elephant: is to :adhesive_bandage: against :notes: OR :heavy_dollar_sign::heavy_dollar_sign::heavy_dollar_sign::heavy_dollar_sign: >>
And :policewoman::policeman: asking about :heavy_dollar_sign:

That was a very huge amount and Rudolph doesn’t have much :heavy_dollar_sign:
so he called :santa:

:deer: -> :santa: hay Santa I’m in a case
:santa: -> :deer:: Hoho hooooo what happen

Rudolph said the incident

:santa: -> :deer: wait I’m coming by my old gang
At that time Santa’s retired reindeers came to Santa’s location

Santa : Oh buddies hets go one of my friends need help
SantaMotorcycle

HO HO HO… how is that …

Suddenly he saw that one lay crying on the road,
Santa is a very helpful man :upside_down_face: :wink: :wink: :wink:
so he asks the other reindeers go for Rudolph help

Santa: hay my lady, why are you crying
Lady: I was in Advent Challenge #8

NewsAdvent of UiPath 2020 and now it was my last one I have no time ti complete due to my office work :sob: :sob: :sob: :sob: :sob: :sob: :sob:

Santa: no worries my lady I will let you my sleigh, give me your phone you can get help form it , when I make a command it will resolve anything I need

LADY GAVE HER TAB TO SANTA
and with in second, he resolves her issues
You wanna see ?? :crazy_face: :crazy_face:

Main.xaml (26.9 KB)
UIPATH APP FILE: GitHub - manee4422/UipathAPP-Challange8: manee4422 / UipathAPP-Challange8

He he he he …
Then Santa //////////////////

Ok, guys now your turn to predict what happened to Santa …

My Prediction :

4 Likes

Merry Christmas roboteers,
This was tricky, so I will only aim for the Beginner level.

Not sure I solved this the best way, but I have a result.
I made a Full Join between Tasks and Executions.
Then I was able to perform the three Tests on one row directly.
Adding the result to a resulting datatable.
To tidy up I remove duplicates and sorted on TaskID before presenting the result.
image
Challenge8Beginner.xaml (19.2 KB)

3 Likes

This has been great fun.
Since I’m new to UiPath, this has been most educational.
First try yourself, then look at how the expertise has solved it.
Merry Christmas to you all!

3 Likes

Hi guys!

In my previous post (Advent Challenge #8 - #20 by dtila), I show you the shortest and fastest way to resolve this challenge.

For those of you who want to learn more about this, I have made a video where I build Advent Challenge #8 robot. Even if the contest is over, it’s worth it for educational purposes

I would love to see feedback from you

Link here: Filtering, LINQ, Invoke custom code | UiPath Advent Challenge 2020 #8 | UiPath Advanced Tutorial - YouTube


Link here: Filtering, LINQ, Invoke custom code | UiPath Advent Challenge 2020 #8 | UiPath Advanced Tutorial - YouTube

Merry Christmas everyone!!!

7 Likes