LINQ to find a value in a column?

[aquinn]

Hi, I have a column called Product Code and a corresponding field called Todays Stock. The file has over 15,000 lines so a for each takes ages.

Is there a LINQ query to help me find the todays stock value using the product code? That returns a value and returns something else if it cannot find it?

I’m trying to learn a bit of LINQ also so if you could explain the answer also I would greatly appreciate it

[Shiva_Nikhil]

@aquinn

if you want to find the value in a specified column

dt.asenumerable.any(function(x) x(“Product Code”).tostring.contains(“Todays Stock”))

gives you a boolean output

cheers

[aquinn]

So if the code for the input I have is 123, it will find 123 in the file and then give me the value in todays stock?

[Shiva_Nikhil]

@aquinn

can you explain what is the input and what is the output you are expecting clearly

[supermanPunch (Arpan)]

@aquinn

Hi, I have a column called Product Code and a corresponding field called Todays Stock

Hi @aquinn ,

I believe Todays Stock is a row value and corresponding to it you would require the Product Code column’s value. If that is the case, then you can check with the below Expression :

DT.AsEnumerable.Where(Function(x)x(0).ToString.Equals("Todays Stock")).Select(Function(x)x("Product Code").ToString).FirstOrDefault

In the above Expression, we have assumed that the row value Todays Stock will be present in the First Column, and hence we have used the index 0.

For Learnings on Linq, You could check the below Posts :

[HowTo] LINQ - First Start # Where Operator

This HowTo introduces how to use the Where operator. Introduction The Where operator is used for filtering. For the filtering, a condition is provided. All items matching this condition will pass the filter and will be part of the returned result. The condition has mandatory to return a boolean outcome. In other words: The result of the condition has to be True or False. Example IPO Samples Comment Values: 20,3,17,22,10,25,5 A collection of Numbers Filter Condition: x > 15 x is …

Do Also note that, we have assumed your data representation also, If you could provide us with the Data format or Representation then we could confirm the working.

[aquinn]

I have a value I will input which will be a code, using this code I need to get the value that is within the todays stock field, so for example if my input was 000892 I would need my output to be 8

[ppr (Peter Preuss)]

@aquinn

find the todays stock value using the product code

it is a typical lookup case.

@aquinn

That returns a value and returns something else if it cannot find it?

we made good experiences when using a lookup dictionary for it (Constraint: all product codes are unique)

dictLookUp =

dtData.AsEnumerable.ToDictionary(Function (x) x("ProductCodeCol").toString.Trim, Function (x) x("Stock").toString.Trim)

before accessing the lookUpDict by dictLookUp(“myProdCode”)

we check with the ContainsKey method.

It could look like this

myStock = IF(dictLookUp.ContainsKey("myProdCode"), dictLookUp("myProdCode"), "NA")

[Shiva_Nikhil]

@aquinn

dict_values=dt.asenumerable.todictionary(function(x) x(“Product Code”).tostring,Function(r) r(“Stock”).tostring)

create a dictionary with name dict_values

assign

outputvalue=dic_values(“000892”).tostring

gives you output as 8

cheers

[aquinn]

@Shiva_Nikhil

dt.asenumerable.todictionary(function(x) x(“Product Code”).tostring,Function(r) r(“Stock”).tostring)

I have put this into a dictionary of and it cannot convert? what variable type do I need?

[aquinn]

Thank you, what variable type would the dictLookUp variable be?

[ppr (Peter Preuss)]

@ppr

dictLookUp

@aquinn

what variable type do I need?

Dictionary(Of String, String)

[Shiva_Nikhil]

@aquinn

You need to intializate the dictionary
New dictionary(of string,string)

In the varaibles panel pass this in the default value of that dictvariable

[ppr (Peter Preuss)]

you got the exception due a wrong datatype was configured

[aquinn]

I am still getting it here

[aquinn]

@Shiva_Nikhil

New dictionary(of string,string)

Even when i initalise it i get this issue

[ppr (Peter Preuss)]

is ensured that all double quotes are of type " and not inverted ones

Also it is recommended to avoid multiple assign activity when implementing never-have-done-before things

[aquinn]

This is the code I am using to convert to dictionary - DT_codes.AsEnumerable.ToDictionary(Function (x) x(“Product Code”).toString.Trim, Function (x) x(“Available Physical Stock - Today”).toString.Trim)

[ppr (Peter Preuss)]

@aquinn

This is the code I am using to convert to dictionary

We would prefer screenshots or code sharing by using </> format button from editor

@ppr

is ensured that all double quotes are of type " and not inverted ones

so we would have to state:

we do see the inverted ones

[aquinn]

@ppr

"

Im sorry i dont understand, if i use ‘’ it just comments the code out? Im new to dictionaries and LINQ so I apologise

[aquinn]

This is the code that is giving me an error - This is what I am using DT_codes.AsEnumerable.ToDictionary(Function (x) x("Product Code").toString.Trim, Function (x) x("Available Physical Stock - Today").toString.Trim)