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!

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

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.

10 Likes

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

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 :

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

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

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.

3 Likes

For Beginner
Hey Santa

3 Likes

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 >
<id>SQLite</id>
<version>1.0.113.0</version>
<authors>Public Domain</authors>
<description>SQLite</description>
<tags>sqlite sql database</tags>
<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 con;
con.Open();

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.ExecuteNonQuery();

//-Open Excel file----------------------------------------------------

ExcelPackage Excel = new ExcelPackage(fi);

for(int row = 2; row <= ExcelTasks.Dimension.End.Row; row++) {
cmd.ExecuteNonQuery();
}

//-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.ExecuteNonQuery();

cmd.ExecuteNonQuery();

//-
//- 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();

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

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();

cmd.ExecuteNonQuery();

//-Output of the Results table----------------------------------------
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.

7 Likes

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

Regards,

Sean

4 Likes

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.

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

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:

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

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.

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

: 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

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.

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.

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

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

6 Likes

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

3 Likes

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.ExecuteNonQuery();
``````

``````//-Output of the Results table----------------------------------------
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
//- then CompletedAllValidRCs is true, otherwise false.
//-
//----------------------------------------------------------------------
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.

5 Likes

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. _
)

)

Function(x) dtExecutions.AsEnumerable.Any( _
) _
) _
)

Function(y) dtExecutions.AsEnumerable.Any( _
AndAlso z("REGIONCODE").ToString=y _
AndAlso z("RESULT").ToString="OK") _
) _
) _
) _
) _
``````

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

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 You have to make a community post on writing LINQ for beginners.

3 Likes

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

2 Likes

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

I think this is the shortest way to resolve this challenge

LINQ:
select new
{
};

``````var taskIsStartedQuery = from taskRow in tasks.AsEnumerable()
select new
{
};

var hasValidRCExcutedQuery = from executionRow in executions.AsEnumerable()
.SelectMany(li => li.Field<string>("RegionCode").Split(new Char[] { ',' }))
select new
{
HasValidRCExcuted = definedRegionCodes.Contains(executionRow.Field<string>("REGIONCODE"))
};
let regionCodes = taskRow.Field<string>("RegionCode").Split(new char[] { ',' })
let completed = from executionRow in executions.AsEnumerable()
select executionRow.Field<string>("REGIONCODE")
select new
{
CompletedAllValidRCs = regionCodes.All(li => completed.Contains(li))
};

select new
{
};

``````

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

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

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)

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.

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

If you wish to watch my Santa Stories…

Episode #1 ==> Advent Challenge #1

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

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

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 the the about Now << this is last is to against OR >>

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

hay Santa I’m in a case
: Hoho hooooo what happen

Rudolph said the incident

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

HO HO HO… how is that …

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

Santa: hay my lady, why are you crying

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

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

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

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