Need help on Linq query

Help Studio
, ,
1

I have an output sheet generated from an process now i need few columns of data to used in as input for another process

Output from process 1 looks like this :

OutPutfromProcess1.xlsx (9.2 KB)

input should be like
Inputsheet.xlsx (10.1 KB)

where for Canada its should be CA , Australia - Aus , and United states - US in column 1

then add the other 3 column same way as it is there then under start date and end date

should be 1st day of month and last date of month same for other rows

data can increase or decrease no duplication in name of country\

i tried this

(From row In dt.AsEnumerable()
Let countryName = row.Field(Of String)(“Country”).ToLower()
Let countryCode = If(countryName = “canada”, “CA”, If(countryName = “united states”, “US”, If(countryName = “australia”, “AUS”, countryName)))
Let startDate = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString(“MM/dd/yyyy”)
Let endDate = New DateTime(now.Year, now.Month, DateTime.DaysInMonth(now.Year, now.Month)).ToString(“MM/dd/yyyy”)
Select dt_Output.Rows.Add(countryCode, Convert.ToInt32(row.Field(Of Double)(“SubscriptionNumber”)), row.Field(Of String)(“OrderNumber”), row.Field(Of String)(“AccountNumber”), row.Field(Of String)(“Email Id”), “ALL”, 500, 500, startDate, endDate)
).CopyToDataTable()

getting error for this section Convert.ToInt32(row.Field(Of Double)(“SubscriptionNumber”))

string not in correct format

3

Hi @mint

Check out the below workflow:
Sequence4.xaml (12.4 KB)

Query:

dt_Output = (From row In dt.AsEnumerable()
 Let countryName = row.Field(Of String)("Country").ToLower()
 Let countryCode = If(countryName = "canada", "CA", If(countryName = "united states", "US", If(countryName = "australia", "AUS", countryName)))
 Let startDate = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("yyyy-MM-dd")
 Let endDate = New DateTime(now.Year, now.Month, DateTime.DaysInMonth(now.Year, now.Month)).ToString("yyyy-MM-dd")
 Select dt_Output.Rows.Add(countryCode, row.Field(Of String)("SubscriptionNumber"), 
                            row.Field(Of String)("OrderNumber"), 
                            row.Field(Of String)("AccountNumber"), 
                            row.Field(Of String)("Email Id"), 
                            "ALL", 
                            500, 
                            500, 
                            startDate, 
                            endDate)
).CopyToDataTable()

Output:
OutPutfromProcess1.xlsx (10.2 KB)
Sheet3-Output is the output sheet

image
image1106×302 7.25 KB

Regards