Copy queue name from one data table to another data table without using loops

[Surya_Narayana_Korivipadu]

Hi UiPath community,

I’ve a data table called “QueueDefinitionsDT” like this:

QueueDefinitionsDT
Name	Id
OEQ	3
HPA	74
Queu3	83
BD	130
VQ	95

Second data table called “QueueItemsDT” like this:

QueueItemsDT
QueueDefinitionId	Status	Key	ProcessingExceptionType	StartProcessing	EndProcessing	Id
130	Successful	cf406435-29b9-42db-b0ce-90fea514577d		08-02-2021 11:35	08-02-2021 11:50	10090
130	Successful	e6e15baf-5fdd-4540-b271-11caa6313145		08-02-2021 14:09	08-02-2021 14:21	10094
130	Successful	7d66b736-ffaa-49f5-a66c-1c059cca81a2	BusinessException	08-02-2021 14:57	08-02-2021 15:06	10095
74	Successful	d0816eb5-379c-45a2-bf1a-7d8b7da9c2d9		08-03-2021 11:00	08-03-2021 11:08	10160
3	Failed	64949faa-11e7-403c-aabe-ab5705c7a072	ApplicationException	08-03-2021 11:22	08-03-2021 11:28	10161
3	Successful	fe771c04-46bd-4589-994e-867b9d3cc65b		08-03-2021 13:52	08-03-2021 14:00	10173

The column “Id” in “QueueDefinitionsDT” and the column “QueueDefinitionId” in “QueueItemsDT” refer to same data. If Id is 3 and the name is “OEQ” in QueueDefinitionsDT, then it means that QueueDefinitionId - 3 in QueueItemsDT refers to the name “OEQ”.

So I need a resulting data table called “FinalQueueDT” like this:

FinalQueueDT
QueueDefinitionId	Status	Key	ProcessingExceptionType	StartProcessing	EndProcessing	Id	QueueName
130	Successful	cf406435-29b9-42db-b0ce-90fea514577d		08-02-2021 11:35	08-02-2021 11:50	10090	BD
130	Successful	e6e15baf-5fdd-4540-b271-11caa6313145		08-02-2021 14:09	08-02-2021 14:21	10094	BD
130	Failed	7d66b736-ffaa-49f5-a66c-1c059cca81a2	BusinessException	08-02-2021 14:57	08-02-2021 15:06	10095	BD
74	Successful	d0816eb5-379c-45a2-bf1a-7d8b7da9c2d9		08-03-2021 11:00	08-03-2021 11:08	10160	HPA
3	Successful	d0816eb5-379c-45a2-bf1a-7d8b7da9c2d9		08-03-2021 11:00	08-03-2021 11:08	10160	OEQ
3	Failed	64949faa-11e7-403c-aabe-ab5705c7a072	ApplicationException	08-03-2021 11:22	08-03-2021 11:28	10161	OEQ

Can someone please guide me how to achieve this without using loops? Because I feel that using loops will take too much to handle huge data.

Please note that QueueItemsDT may not contain all queue IDs, but QueueDefinitionsDT contains all queue names and IDs. Whichever queues are present in QueueItemsDT, I need to match a name to it based on QueueDefinitionsDT

@Shubham_Varshney @ppr

[ppr (Peter Preuss)]

we can do following:

FinalQueueDT= QueueItemsDT.Clone
Add DataColumn Activity - Add the QueueName DataColumn to the empty/cloned FinalQueueDT

Assign Activity:
Left side: FinalQueueDT
Right side:

(From d in QueueItemsDT.AsEnumerable
Let qn = QueueDefinitionsDT.Where(Function (f) f("Id").toString.Trim.Equals("d("QueueDefinitionId").toString.Trim)).Select(Function (x) x("Name").toString).DefaultIfEmpty("Undefined").First()
Let ra = d.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable

[Surya_Narayana_Korivipadu]

@ppr

Thank you for the response.

The id column name in QueueItemsDT is “QueueDefinitionId” which matches with QueueDefinitionsDT - Id column. So I assume I should use d(“QueueDefinitionId”) in second line of your linq query. Am I correct?

[ppr (Peter Preuss)]

Yes, you are right. I did an update on the LINQ.

[Surya_Narayana_Korivipadu]

@ppr I’ve seen the update.

I may not be able to build such query.

But as per my understanding it should be f(“Id”) and then d(“QueueDefinitionId”), because f belongs to QueueDefinitionsDT and d belongs to QueueItemsDT. Can you please correct me if my understanding is wrong?

I assume your linq query is taking each Id from QueueDefinitionsDT and trying to match to QueueDefinitionId for every row in QueueItemsDT and then preparing Name column in FinalQueueDT. Am I correct? If not, can you please elaborate what this linq query does?

[ppr (Peter Preuss)]

You are correct. It was just swapped. Can you test again?

Let qn = QueueDefinitionsDT.Where(Function (f) f("Id").toString.Trim.Equals("d("QueueDefinitionId").toString.Trim)).Select(Function (x) x("Name").toString).DefaultIfEmpty("Undefined").First()

Here we are searching in QueueDefinitionsDT for the Id from our QueueItemsDT row
In case of we do not find then we are returning a default value “Undefined” in our imp

[postwick (Paul)]

Why would you copy the value from one table to another? This is two tables in a relational format, and you can use a SQL join to get the resulting data you want without actually copying the value. This is valuable, because if something changes in one table, you don’t have to worry about updating the other table - it’s relational. The point to relational data is you don’t have to copy values from one table to another.

SELECT * FROM QueueDefinitionsDT.*, QueueItemsDT.* FROM QueueItemsDT INNER JOIN QueueDefinitionsDT ON QueueDefinitionsDT.Id = QueueItemsDT.QueueDefinitionId

[Surya_Narayana_Korivipadu]

I’m aware of Joins concept. But I’m not sure if inner join can give me my result, because

1. the no. of rows in QueueItemsDT and QueueDefinitionsDT are not same
2. QueueItemsDT contains many rows of data where QueueDefinitionId can be a duplicate.

Anyways, thank you for the response. I’ll try to understand and try it in my code.

[Surya_Narayana_Korivipadu]

Hi @ppr

I’m trying this linq. I got an error “Where is not a member of System.Data.Datatable”

[ppr (Peter Preuss)]

Sorry, today is Bug day. Corrected
From d in QueueItemsDT.AsEnumerable

[Surya_Narayana_Korivipadu]

ha ha. no problem. I’m still learning linq. So it’s taking time for me to understand the complex queries.

Thank you very much for your quick responses. I’ll try this.

[ppr (Peter Preuss)]

For learning LINQ also hava look here:

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operator [HowTo] Overview Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start…

Are the datatable stored in a datavase? Does mean do you retrieve it via SQL Statements?

[Surya_Narayana_Korivipadu]

yes. I’m learning from tutorialsteacher.com as you said in your previous replies to my another posts.

Sorry to bother you once again. Data is not in DB. It’s directly in data table variables. I tried .AsEnumerable for both data tables and I got the following error:

Multiple Assign - Final Queue DT: Can not assign ‘(From d In QueueItemsDT.AsEnumerable
Let qn = QueueDefinitionsDT.AsEnumerable.Where(Function (f) f(“Id”).tostring.trim.equals(d(“QueueDefinitionId”).tostring.trim)).Select(Function (x) x(“Name”).tostring).DefaultIfEmpty(“Undefined”).First()
Let ra = d.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable’ to ‘FinalQueueDT’.

I’m not able to understand what went wrong. Can you please help?

[ppr (Peter Preuss)]

So i cross checked last version and it was valid:

May we ask you to compare to your implementation thanks

@Surya_Narayana_Korivipadu

Data is not in DB. It’s directly in data table variables.

OK, so things cannot be done in SQL directly

As an alternate find a inner join approach here:

(From d1 In QueueItemsDT.AsEnumerable
Join d2 In QueueDefinitionsDT.AsEnumerable
On d1("QueueDefinitionId").toString.Trim Equals d2("Id").toString.Trim
Let qn =d2("Name").tostring
Let ra = d1.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable

Ensutre that from Copy & Paste all " are correctly taken and no “ or ” is used

[postwick (Paul)]

@Surya_Narayana_Korivipadu

the no. of rows in QueueItemsDT and QueueDefinitionsDT are not same

Doesn’t matter. Inner Join will only give you matching rows. Outer Join will give you all rows from one table, plus matching rows from the other table.

“QueueItemsDT contains many rows of data where QueueDefinitionId can be a duplicate.”

Doesn’t matter.

[Surya_Narayana_Korivipadu]

Hi @ppr

May be I gave extra new lines in my query. I’ll cross check once again and I’ll get back probably next week.

By the way, thank you for sharing the query with join method

[Surya_Narayana_Korivipadu]

Hi @ppr

This linq which prepares FinalQueueDT is valid syntactically. I cross checked and it looks correct. Also updated case properly in toString, Trim and Equals. Still I’m getting same error.

Here’s linq:

Tried to assign this to FinalQueueDT. I got the error. So created one more DT as FinalQueueDT2, but still I’m getting same error.

I haven’t tried the join query yet. I’ll try that too

[ppr (Peter Preuss)]

can you please share last validation message?

[Surya_Narayana_Korivipadu]

Did you mean error message?

Here it is:
Multiple Assign - Final Queue DT: Can not assign ‘(From d In QueueItemsDT.AsEnumerable
Let qn = QueueDefinitionsDT.AsEnumerable.Where(Function (f) f(“Id”).toString.Trim.Equals(d(“QueueDefinitionId”).toString.Trim)).Select(Function (x) x(“Name”).toString).DefaultIfEmpty(“Undefined”).First()
Let ra = d.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable’ to ‘FinalQueueDT’.

This time I used “FinalQueueDT” in both left hand side and right hand side of assign activity

[ppr (Peter Preuss)]

use single assign
use statement as tested and provided (Post 14)
About ValidationMessage - if it is failing then create screenshot from hover containing the message
Test alternate Join Syntax

Thanks