LINQ query error

Help Studio
, ,
1

Hi Team,

I am using this query.
(From a In Sumif_DT.AsEnumerable()
Join b In MB52_Data_DT.AsEnumerable()
On a.Field(Of String)(“Material”) Equals b.Field(Of String)(“Material”) And a.Field(Of String)(“Plant”) Equals b.Field(Of String)(“Plant”) And a.Field(Of String)(“Unit”) Equals b.Field(Of String)(“Base Unit of Measure”) And If CDbl(a.Field(Of String)(“Storage Location”)) Equals CDbl(b.Field(Of String)(“Storage Location”))
Where CInt(a.Field(Of String)(“Quantity”))>= CInt(b.Field(Of String)(“Unrestricted”))
Select notvalidmaterial_DT.Rows.Add({a.Field(Of String)(“Material”)})
).CopyToDataTable

But i need one update in this. Actually the Storage Location column also contains blanks. So, i need to convert only the non blank ones to double and then check is it equal or not. Because now the error is coming like conversion is not valid. Because of the blanks.

Please help.

2

(From a In Sumif_DT.AsEnumerable()
Join b In MB52_Data_DT.AsEnumerable()
On a.Field(Of String)(“Material”) Equals b.Field(Of String)(“Material”) And
a.Field(Of String)(“Plant”) Equals b.Field(Of String)(“Plant”) And
a.Field(Of String)(“Unit”) Equals b.Field(Of String)(“Base Unit of Measure”) And
(String.IsNullOrEmpty(a.Field(Of String)(“Storage Location”)) Or CDbl(a.Field(Of String)(“Storage Location”)) Equals CDbl(b.Field(Of String)(“Storage Location”)))
Where CInt(a.Field(Of String)(“Quantity”)) >= CInt(b.Field(Of String)(“Unrestricted”))
Select notvalidmaterial_DT.Rows.Add({a.Field(Of String)(“Material”)})
).CopyToDataTable

Check this out
Regards

1 Like
4

Hi @yash.choursia

Try this

(From a In Sumif_DT.AsEnumerable()
Join b In MB52_Data_DT.AsEnumerable()
On a.Field(Of String)("Material") Equals b.Field(Of String)("Material") And
   a.Field(Of String)("Plant") Equals b.Field(Of String)("Plant") And
   a.Field(Of String)("Unit") Equals b.Field(Of String)("Base Unit of Measure") And
   (String.IsNullOrWhiteSpace(a.Field(Of String)("Storage Location")) Or
    (Not String.IsNullOrWhiteSpace(a.Field(Of String)("Storage Location")) And
     CDbl(a.Field(Of String)("Storage Location")) Equals CDbl(b.Field(Of String)("Storage Location")))) And
   CInt(a.Field(Of String)("Quantity")) >= CInt(b.Field(Of String)("Unrestricted"))
Select notvalidmaterial_DT.Rows.Add({a.Field(Of String)("Material")})
).CopyToDataTable

I hope it works!!

5

@yash.choursia
(From a In Sumif_DT.AsEnumerable()
Join b In MB52_Data_DT.AsEnumerable()
On a.Field(Of String)(“Material”) Equals b.Field(Of String)(“Material”) And a.Field(Of String)(“Plant”) Equals b.Field(Of String)(“Plant”) And a.Field(Of String)(“Unit”) Equals b.Field(Of String)(“Base Unit of Measure”)
Where CInt(a.Field(Of String)(“Quantity”))>= CInt(b.Field(Of String)(“Unrestricted”)) And a.Field(Of String)(“Storage Location”) IsNot String.Empty And b.Field(Of String)(“Storage Location”) IsNot String.Empty
Select notvalidmaterial_DT.Rows.Add({a.Field(Of String)(“Material”)})
).CopyToDataTable

1 Like
6

If a column contain “abc123”. what should we convert this to. I am unable to convert it to string. Showing as unable to cast double to string

7

@yash.choursia Is this for @B_H_Akshatha_Pai solution?

8

image
image1365×476 26.8 KB

Please help

9

No No. I wanted to reply to you. I replied to other message by mistake

10

Have you tried this?

From a In Sumif_DT.AsEnumerable()
Join b In MB52_Data_DT.AsEnumerable()
On a.Field(Of String)(“Material”) Equals b.Field(Of String)(“Material”) And a.Field(Of String)(“Plant”) Equals b.Field(Of String)(“Plant”) And a.Field(Of String)(“Unit”) Equals b.Field(Of String)(“Base Unit of Measure”)
Where CInt(a.Field(Of String)(“Quantity”))>= CInt(b.Field(Of String)(“Unrestricted”)) And a.Field(Of String)(“Storage Location”) IsNot String.Empty And b.Field(Of String)(“Storage Location”) IsNot String.Empty
Select notvalidmaterial_DT.Rows.Add({a.Field(Of String)(“Material”)})
).CopyToDataTable

11

Hi @yash.choursia ,

Could you provide us with a Sample Input Data and its Expected Output, so we can conclude on what operations that we can do or minimise.

It seems that you are only selecting one Column for the Output, So there are possibility that we can reduce the expression.

12

I want to check my Storage location column as well @Brian_Mathew_Maben. My Storage Location column contains 4 digit number and blanks as well. So while converting it to double becuase of the blanks, it not allowing me to convert the blanks to double. Please let me know how to convert the non blanks to double and check the value. Here you have not checked the value. No equals is there in the Storage Location column

13

@yash.choursia Can you share the input data

14

Actually @supermanPunch . I have done the same thing using 2 for loop and 2 if. I am sharing it here. I want the exact same thing in LINQ.

image
image644×597 25.2 KB

The First id condition is:
CurrentRow_Sumif.Item(0).ToString = CurrentRow_MB52.Item(0).ToString AND CurrentRow_Sumif.Item(3).ToString = CurrentRow_MB52.Item(3).ToString AND CurrentRow_Sumif.Item(1).ToString = CurrentRow_MB52.Item(5).ToString AND CurrentRow_Sumif.Item(2).ToString = CurrentRow_MB52.Item(8).ToString

and the second if condition is:
Integer.Parse(CurrentRow_Sumif.Item(4).ToString) < Integer.Parse(CurrentRow_MB52.Item(7).ToString)

15

Just shared the use case using 2 for statement and 2 if statement. I want the exact same thing in LINQ.

16

@yash.choursia ,

Maybe we need not convert to Double, as also provided with the For Each If Representation.

Check if the below Expression works for your case :

(From a In Sumif_DT.AsEnumerable()
Join b In MB52_Data_DT.AsEnumerable()
On a("Material").ToString Equals b("Material").ToString And
a("Plant").ToString Equals b("Plant").ToString And
a("Unit").ToString Equals b("Base Unit Of Measure").ToString And a("Storage Location").ToString Equals b("Storage Location").ToString
Where CInt(a("Quantity").ToString) >= CInt(b("Unrestricted").ToString)
Select notvalidmaterial_DT.Rows.Add({a("Material").ToString})
).CopyToDataTable

We do think that there are still more possible ways to minimise this Expression but we will not be sure as we have not received the Sample Inputs/Outputs.

Also, a good start should be with the Join Datatables activity and check if anything does go wrong with it and then switch to Linq.

Let us know if the above expression doesn’t work or gives irregular outputs.

17

From dt1 In Sumif.AsEnumerable
Join dt2 In MB52.AsEnumerable
On dt1(0).ToString Equals dt2(0).ToString And dt1(3).ToString Equals dt2(3).ToString And dt1(1).ToString Equals dt2(5).ToString And dt1(2).ToString Equals dt2(8).ToString
Where Integer.Parse(dt1(4).ToString) < Integer.Parse(dt2(7).ToString) And dt1(“Storage Location”).ToString IsNot String.Empty And dt2(“Storage Location”).ToString IsNot String.Empty
Select notvalidmaterial_DT.Rows.Add({dt1(“Material”).ToString})

The above query will give row values if Storage Location is not empty and your conditions satisfy. To remove conversion to double error remove CDbl to avoid conversion to double

18

You may use this solution also if usable:
(From a In DT1.AsEnumerable()
Join b In DT2.AsEnumerable()
On a(“Material”).ToString Equals b(“Material”).ToString And a(“Plant”).ToString Equals b(“Plant”).ToString And a(“Unit”).ToString Equals b(“Base Unit Of Measure”).ToString And
Double.TryParse(a(“Storage Location”).ToString,0) Equals Double.TryParse(b(“Storage Location”).ToString,0)
Where CDbl(a(“Storage Location”).ToString) = CDbl(b(“Storage Location”).ToString)
Where CInt(a(“Quantity”).ToString)>= CInt(b(“Unrestricted”).ToString)
Select notvalidmaterial_DT.Rows.Add({a(“Material”).ToString})
).CopyToDataTable

19

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