Advent Challenge #8

[dianamorgan]

Welcome to the 2020 Advent Challenge #8!

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
→ : Digi Guru Incorporation, My name is Rudolph what can I do for you?
→ : Ha Ha funny. Hello Rudolph, it’s me, Santa. How are you?
→ : Hello Santa, How are you? I was assuming this call would be my first client.
→ : Unfortunately not. I am running under non profit conditions.
→ : Well, I will help you. Tell me.
→ : My → and about issues in the of some tasks executions
→ : Oh. Sounds like
-> : Yes. I would the UiPath Forum for , but the data is and I cannot to
→ : Sorry, the . I will you back.

→ : Hi Santa, here I am back.
→ : What happened?
→ :
the the about Now << this is last is to against OR >>

→ : Oh, So you are not
→ : Do following: the 001010010. Just call it T#01 instead of for etc.
→ : Sounds good. But the complex . How will the from UiPath Forum this?
→ : Just your Business Requirements and it down. This that 01011010 is

→ : Thanks Rudolph
→ : You’re welcome

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 #8:

• input: DataAdvent#8.xlsx (10.2 KB) containing data within 2 worksheets:
  • Task data: Worksheet name: Tasks
  • Execution data: Worksheet name: Executions
• Result: Report with applied Validation Rules result

Rules:
Check for isDefinedTask Rule:

• if a task is present in Execution data, but not defined in Task data then isDefinedTask = False
  else: isDefinedTask = True

Check for TaskIsStarted Rule

• if a task is defined in Task data, and also present in Execution Data then TaskIsStarted = True
  Else TaskIsStarted = False

Check for hasValidRCExcuted Rule:

• if a Task in Execution data is executed in a region which is not defined in Task data for this Task ID then hasValidRCExcuted = False
  else hasValidRCExcuted = True
  if the task was never executed: hasValidRCExcuted = False

To Do:

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

Sample result row would look like this:

TaskID	isDefinedTask	TaskIsStarted	hasValidRCExcuted
T#**	True	False	False
** = the corresponding Task No, but we do not spoil it

Advanced Advent Challenge #8:

• input: DataAdvent#8.xlsx (10.2 KB) containing data within 2 worksheets:
  • Task data: Worksheet name: Tasks
  • Execution data: Worksheet name: Executions
• Result: Report with applied Validation Rules result

Additional Rule:
Check for CompletedAllValidRCs Rule

• The executions of task in execution data with the status “OK” are relevant
  if a task is executed for all RegionCodes defined for this task in the Task data then: CompletedAllValidRCs = True
  Else: CompletedAllValidRCs = False
  If the task was executed on not defined RegionCodes it will not count and change the CompletedAllValidRCs Check result

To Do:

• Solve the Beginner Assignment + the Advanced Assignment
• Upload evidence (screenshot/s) that you successfully obtained the results using UiPath.
  AND Upload your XAML/uiPathproject as ZIP in your reply
• Elaborate on how easy the integration of the CompletedAllValidRCs Rule was done into your Beginner solution

Sample result row would look like this:

TaskID	isDefinedTask	TaskIsStarted	hasValidRCExcuted	CompletedAllValidRCs
T#**	True	True	True	False
** = the corresponding Task No, but we do not spoil it

Deadline:

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

Bonus points will be awarded for

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

[stefano_negro]

Hello Santa,

so we came to help you for the final time!!
I sincerely hope us users have been able to help you and you have been satisfied with our little bots!

What? You have one final request for us? Ok, let’s try to solve this!

Beginner Challenge

In order to solve the challenge I split the process for every check, starting with TaskIsStarted:
this is done with a inner loop and a check, if the same task is found then the result is true, false otherwise.

Similar thing is done for isDefined, but this time the inner loop is done in the Task DT

Finally, for the hasValidRC check, we loop in the Result DT (already filled with all tasks from previous checks) and check if isStarted is true

if is the case, we filter the Execution sheet for all the executions of the task and the Task sheet for the TaskID.
If there are valid rows, we check if it contain the RegionCode, otherwise it is marked as False

Advanced Challenge

For the advanced Challenge we just have to add a check inside the final one, that set CompletedAllValid a true if all the Results are OK, False otherwise

So this is the final result, ordered for TaskID

AdvChallenge8.xaml (37.9 KB)

Also, if you need a bot that can help you deliver all the gifts on Christmas… I’m sorry but we can’t do that, it has already been tried and didn’t end well…

Merry Christmas to all UiPath Team and thank you for this great challenge!!!

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

Santa, you did ensure that this challenge was a challenge! Thoroughly enjoyed solving this. Learnt a cool function in C# ListOfRegion.Except(RegionCode).ToList() . Thank you for this hidden pearl in the oyster!

Task 1:

Task 2:

Task 3:

Task 4: The of a task. The crescendo of the concert!

Finally we sort the datatable and write it to an excel file.

SANTA! The kids are waiting in many regions, job status shows there is still work to do… chop chop

Here is my submission file for the Advanced Advent Challenge # 8 (Run JobStatus.xaml):
AdvancedAdventChallenge8.zip (18.2 KB) :

Thank you for organizing this for the community. Every challenge was fun, but if I have to pick one, then it definitely is Challenge #4. Was great fun, I guess it is something about playing with data in bytes and images!

I wish everyone merry Christmas and a happy (healthy) new year.

[Veena_Siva]

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!!!

[Anna_Maziarz (Anna Skrzeszowska - Office Samurai)]

Beginner
Here is my solution for Beginner challenge.
It was not an easy challenge, but I learned a few new things
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 :

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 !

[Priyanka_Ramesh]

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 :

I tried the solution using Linq queries.

[Adee (Muhammad Adeel)]

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

[StefanSchnell]

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 . 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:

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

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:

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.

[seanrockvz13 (Sean Jerome Llanto)]

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

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

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!

Merry Christmas to everyone!!

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

Advent Challenge 8.zip (15.5 KB)

Regards,

Sean

[abu.behlim (Abu Hurera Behlim)]

Beginner

Hello Santa,

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

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

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

I have sent the file on your Email ID

Ok Santa I got the file.

So here is the workflow for this challenge.

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.

---

2. 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

3. 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:

Thank you Mr.Alien for helping me.

Can I take a picture with you Mr.Alien

Why not santa Ok wait, Let me turn on my UEFO Fleverybodyash

1… 2 … 3

And CLICK

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

AdventChallenge_8_Beginner.zip (18.3 KB)

Advanced

4. 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.

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.

[monsieurrahul (Rahul)]

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

: 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

: 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:

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:

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!

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 and a Happy New Year

Stay safe!

Cheers,
Rahul

[monsieurrahul (Rahul)]

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

[abu.behlim (Abu Hurera Behlim)]

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

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

[StefanSchnell]

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.

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.

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.

[Yoichi]

(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

[monsieurrahul (Rahul)]

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

[abu.behlim (Abu Hurera Behlim)]

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

[dtila (Daniel)]

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

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

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!

[seanrockvz13 (Sean Jerome Llanto)]

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

Another four legged creature faced the challenge and volunteered!

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

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.