Need to do a vlookup and convert column data into Row data using LINQ

Hello Experts,

I have a situation where I need your help with LINQ only.

  1. I need do a vlookup from Data Table 1 to Data Table 2 and fetch the Week Format and write into a TempDT.

  2. The second part is to convert the TempDT from Column to Row Data into Final DT.

I am also attaching the excel file.

Thank you in advance for support.

Regards,
ManjeshPivot.xlsx (30.3 KB)

Dear @Palaniyappan @prasath17 ,

Good Day.

Help needed here.

Regards,
Manjesh

1 Like

Try this for Vlookup

(From a In dtSheet1.AsEnumerable() Join b In dtSheet2.AsEnumerable() On a(“Week Day”).ToString Equals b(“Week Day”).ToString Select dtSheet3.LoadDataRow (new Object() { a.Field(Of String)(“Week Day”), b.Field(Of String)(“NAME”),a.Field(Of String)(“Qty”), a.Field(Of String)(“Part Number”), },False)).CopyToDataTable

2 Likes

Hello Aleem,

There is an error “Expression expected”.

(From a In DemandDT.AsEnumerable() Join b In WeekFormatDT.AsEnumerable()
On a(“Week Day”).tostring Equals b(“Week Day”).tostring
Select dtSheet3.LoadDataRow (New Object() { a.Field(Of String)(“Week Day”), b.Field(Of String)(“NAME”), a.Field(Of String)(“Qty”), a.Field(Of String)(“Part Number”), },False)).copyToDataTable

Regards,
Manjesh

1 Like

Hello Aleem,

I removed the , before the end of the flower braces and it worked. :face_with_monocle:

What if I need to add the “Week Format” column from dtSheet2 as a new column in dtSheet1 rather than a new data table.

Thank you.

Regards,
Manjesh

1 Like

There is active for add data column

@manjesh_kumar

Creating tempDT
grafik

Preparing Pivot:

Creating Pivot:
grafik
grafik

Find starter help here:
Pivot_Demo1.xaml (14.2 KB)
Pivot.xlsx (35.2 KB)

Kindly note:
It can happen that read range will force the dates internally to different formats and causes that dtTemp creation will fail. In such case always check within a debug what are the values in dt1, dt2 and force or adopt the values before joining to dtTemp.

This behaviour is not a bug in LINQ, it is a behaviour of the result from Read Range

1 Like

Hi @manjesh_kumar
Hope this code helps you.
BlankProcess9.zip (72.7 KB)

Dear Peter,

Good Day.
I ran the .xaml file with my actual data and it is giving the following error.

Add Data Column: A column named ‘W202102’ already belongs to this DataTable. I believe the sample data was not enough. My apologies for that.ScrapedData.xlsx (32.2 KB)

I am attaching the actual data for your reference.

Regards,
Manjesh

@manjesh_kumar

give a try on following:

arrOrderedWK =

(From d In dtTemp.AsEnumerable
Let w = CInt(Regex.Match(d("Week Format").toString,"\d+").toString)
Order By w 
Select d("Week Format").toString.Trim).Distinct().toArray

Dear Peter,

Good Day.

The issue got resolved now.

However I can see that there is some kind of pattern where it is fetching only 7 records per part number.

There are 53 weeks only for the part number Apple so definitely in dtTemp the records should be much higher.

I have removed the duplicates for Part number and Week Date and attached the file again, and there are about 1219 records.

ScrapedData.xlsx (31.4 KB)

Regards,
Manjesh

Hello @Gresilda_Balla1 ,

Good Day.

I tried your solution too. I am getting the following error. Please use the ScrapedData.xlsx for further testing which was posted after the initial post.

Regards,
Manjesh

can you please share your current implementation (e.g. the xaml ) with us? thanks

Dear Peter,

Please find attached the xaml file and the source file.

Pivot_Demo1.xaml (14.9 KB)
ScrapedData.xlsx (49.8 KB)

Regards,
Manjesh

Dear Peter,

Good Day.

Were you able to find a solution…

Regards,
Manjesh