Convert datatable to collection by validating values

Help Studio
, , ,
1

I would like to convert the following DataTable into a collection:
image
where the keys are strings and the values are lists of strings, the keys will have the User ID and the lists will have all the Invoices ID that belong to that user. I am using LINQ and so far I have the following statement:

dt.AsEnumerable().GroupBy(Function(r) r("User"), Function(r) r("Invoice")).ToDictionary(Function(g) g.Key, Function(g) g.ToList())

But I would like to be able to validate that only the elements appearing in the collection (Users and Invoices) are the ones where the column “Existence” is equal to “Y”. Could someone help me add this validation in my instruction? How can I validate this to only add to the collection the values ​​where the invoices exist?

2

What’s the obsession lately with everyone trying to do all this stuff with queries?

Just loop through the DT. If the Existence value is Y then add the Invoice ID to the dictionary.

As long as your dictionary is of string, object, then the value (object) can be a list. You can interact with the dictionary value the same as any other list. So if you have a key of 432432 and its value is a list object, just add to the list myDict(“432432”) the same as you would any other list.

3

I only want to make my workflow cleaner and more efficient!! because currently I already have many loops and cycles, and the same process would be very repetitive. I thought about doing it as you mentioned but I would like to make everything simpler and more efficient! :))

4

give a try on:
(From d In dt.AsEnumerable.Where(Function (x) x(“Existence”).toString.trim.Equals(“Y”))
Group d By k1= d(“User”).toString.Trim Into grp=Group
Let ivl = grp.Select(Function (x) x(“Invoice”).toString.Trim).toList
Select t=Tuple.Create(k1, ivl )).ToDictionary(Function(t) t.Item1, Function(t) t.Item2)

Filtering out all non Y rows before grouping, groups data on User

5

Using easily-readable activities instead of complicated queries does make your workflow cleaner and more efficient.

6

How are you separating the lines? because writing it is expecting ‘(’ or ‘)’

7

didnt get your question? Can you please tell different? Thanks
Maybe you can share an expected out description with us.

8

lololool
lololool1111×266 19.6 KB

“Se esperaba” = “expected”

9

ah, sorry did correct the statement. first line ommited a closing )

12

It is giving me the values ​​in the wrong format, what I am looking for is to save it in a collection where the key is string and the value is a list of strings. The list of strings must be all the invoice IDs that belong to the user.

13

what is current output / where it differs. Can you please share sample / results. Thanks
grafik
we did understand that yellow ones are to use for the grouping, right?

Also have a try on

(From d In dt.AsEnumerable.Where(Function (x) x(“Existence”).toString.trim.Equals(“Y”))
Group d By k1= d(“User”).toString.Trim Into grp=Group
Let ivl = grp.Select(Function (x) x(“Invoice”).toString.Trim).toList
Select t=Tuple.Create(k1, ivl )).ToDictionary(Function(t) t.Item1, Function(t) t.Item2)

here the dictionary value is the list of Invoice Nos of the Group

1 Like
14

This last example worked, thank you very much!

15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.