Adding the data from (stored variable )- data table to append the Excel sheet

supermanPunch · February 27, 2023, 4:55pm

Would these two values be present in the Column Code along with Laptop, Desktop and Telivision ?

What is the result/output that you receiving now for the current workflow suggested with the original input ?

vr10 · February 27, 2023, 5:47pm

Would these two values be present in the Column Code along with Laptop , Desktop and Telivision ?—No

In Column Code "Services" only along with these 3(Laptop,DesktopandTelivision)

In Category code
(Code column)services --Premier Support(Category column)
(Code column)services --Deployment/Recovery Services(Category column)
like that is there these 2.

Now i am getting answer correct as per sample file. but if i chagned some variables like (Support , Mobsupportservice )like iam getting wrong because its Varibales so.

If i want to change the variables how is it in Future also (like i have to change in Vb.net Query only)

Thanks
(Is it possible to contact you)

vr10 · February 27, 2023, 5:48pm

Hi
Would these two values be present in the Column Code along with Laptop , Desktop and Telivision ?—No

In Column Code "Services" only along with these 3(Laptop,DesktopandTelivision)

In Category code
(Code column)services --Premier Support(Category column)
(Code column)services --Deployment/Recovery Services(Category column)
like that is there these 2.

Now i am getting answer correct as per sample file. but if i chagned some variables like (Support , Mobsupportservice )like iam getting wrong because its Varibales so.

If i want to change the variables how is it in Future also (like i have to change in Vb.net Query only)

Thanks
(Is it possible to contact you)

supermanPunch · February 27, 2023, 6:06pm

@vr10 ,

We would require to know what would be the output for the original type of format.

Do note that in your initial sample input, you have only mentioned Mobile in the Code column and Support in the Category, but now we have two values in such a manner Services - Premier Support and Services - Deployment/Recovery Services. We would need to know if there are more than one values what needs to be done for each of the case.

For the Mobile Support, the logic was to copy it’s Qty value to the Support column and also copy the same to the other Code type values. But when we have two values now, what would need to be done ?

If provided an example case, we should be understand, Keep in mind the dynamics that would be present when providing us with sample cases to work on.

vr10 · February 27, 2023, 6:46pm

Thanks for Knowing,

I think after the answer as per requirement i have to change but here i can’t change the variable so i don’t now this scenario.

Present i have 2 Services future i have to add one More.
Input File:

I have showed above is Input Sheet First i have filter to work ((Code)Support- Premier Support(Category)) in one sheet(like support, total, Percentage and Remarks).

like same i have to work another sheet:(Code column)services --Deployment/Recovery
Services(Category column).

In future if i get one more i have to add like that and work

Thanks

supermanPunch · February 27, 2023, 7:15pm

@vr10 ,

Could you provide the Expected Output data for this Input sheet ?

vr10 · February 27, 2023, 7:50pm

Hi,

Input file will combine all the services first i will filter and Add the data in sheet name as “Premier support” and next sheet as “Deployment/Recovery” like that we have to work.

In Premier support-sheet we will shown only that qty work
In “Deployment/Recovery”-sheet we will shown only that qty work like that we have work

Remaining everything same only filter services as per requirement.

Procedure is same just we have change as per Requirement to filter “Premier support” And “Deployment/Recovery”.

I hope your understood.

Thanks

vr10 · February 28, 2023, 9:07am

Hi .

Any update for this code…

Thanks

supermanPunch · February 28, 2023, 9:53am

@vr10 ,

If that is the case, then we would need to add a few more steps to the process or modify the existing process :

After adding all the Data columns, we would need to use a For Each Activity and get the unique values present in the column Category (Premier support, Deployment services). For that purpose, we could use the Below Expression in the For Each Activity.

DT.AsEnumerable.Where(Function(x)Not(String.IsNullOrWhiteSpace(x("Category").ToString) Or x("Category").ToString.Trim.Equals("-"))).Select(Function(x)x("Category").ToString).Distinct.ToArray

Next, Inside the For Each Activity, we would need to perform the Filter for the Input datatable with current Category item that is iterating through and use the Filtered Datatable for Calculating Support, Total, Percentage and Remarks. Filtering can be done using the Filter Datatable Activity.

Next, we use the Same Expression provided before to Generate the Output datatable required using the Assign Activity. But now, we are using the TempDT as the input, as it is the Filtered datatable for each Category that we have to use.

(From d1 In TempDT.AsEnumerable
Group d1 By k=d1("Number").toString.Trim Into grp=Group
From g In grp.DefaultIfEmpty
Let onlyReqGrp = grp.Where(Function(x){"Laptop","Desktop","Telivision"}.Any(Function(y)y.ToString.ToLower.Equals(x("Code").ToString.ToLower))).ToArray
Let SupportGrp = grp.Where(Function(x)Not({"Laptop","Desktop","Telivision"}.Any(Function(y)y.ToString.ToLower.Equals(x("Code").ToString.ToLower)))).ToArray
Let support = If(SupportGrp.Any,SupportGrp.Sum(Function(x)CDbl(x("Qty"))),0)
Let total = onlyReqGrp.Sum(Function(x)CDbl(x("Qty")))
Let percentage = If(total=0 OrElse support=0,0,Math.Round((CDbl(support)/total)*100))
Let remarks = If(onlyReqGrp.Any,"("+String.Join("+",onlyReqGrp.Select(Function(x)x("Code").ToString))+")","No Values")
Select ra = g.ItemArray.Concat({support,total,percentage.ToString+"%",remarks}).ToArray
Select OutputDT.Rows.Add(ra)).CopyToDataTable

Last, we use the Write Range Activity and write the Output datatable OutputDT generated into the Excel sheet. For the sheet name you could use iterative variable category shown in the image below.

The whole modification would look like below :

Compare the Steps and the whole view provided, you should be able to implement the workflow.
The workflow is the same, the part/requirement which was missed out earlier was multiple outputs need to be generated for each category type.

I believe this is what was required.

vr10 · February 28, 2023, 10:40am

Hi,

Sorry For Interrupting again and again.

Replied in 16th:This code was perfectly working just for varibale that is mobileSupportGrp (Because i want to change instead of this

(Code column)services (Mobile Replace Services)–Premier Support(Category column)(Support Replaces)
(Code column)services(Mobile Replace Services) --Deployment/Recovery Services(Category column)(Support Replaces)
that’s it Apart from everything Fine
see Output file:

If i get 2 code also fine first one for: Services-Premier Support
2nd one for :Services-Deploymeny/RecoveryServices.

Please change it the variable names

(From d1 In DT.AsEnumerable
Group d1 By k=d1(“Number”).toString.Trim Into grp=Group
From g In grp.DefaultIfEmpty
Let onlyReqGrp = grp.Where(Function(x){“Laptop”,“Desktop”,“Telivision”}.Any(Function(y)y.ToString.ToLower.Equals(x(“Code”).ToString.ToLower))).ToArray
Let mobileSupportGrp = grp.Where(Function(x)Not({“Laptop”,“Desktop”,“Telivision”}.Any(Function(y)y.ToString.ToLower.Equals(x(“Code”).ToString.ToLower)))).ToArray
Let support = If(mobileSupportGrp.Any,mobileSupportGrp.Sum(Function(x)CDbl(x(“Qty”))),0)
Let total = onlyReqGrp.Sum(Function(x)CDbl(x(“Qty”)))
Let percentage = If(total=0 OrElse support=0,0,Math.Round((CDbl(support)/total)*100))
Let remarks = If(onlyReqGrp.Any,“(”+String.Join(“+”,onlyReqGrp.Select(Function(x)x(“Code”).ToString))+“)”,“No Values”)
Select ra = g.ItemArray.Concat({support,total,percentage.ToString+“%”,remarks}).ToArray
Select OutputDT.Rows.Add(ra)).CopyToDataTable

Thanks

supermanPunch · February 28, 2023, 10:43am

@vr10 ,

Were you able to implement the Steps provided in my previous post ?

Did you not receive the output as expected ?

The suggested changes should work for dynamic values in the category column, Even if you have more than 2 values in Category, it should give you the required output.

The number of output sheets generated will depend on the number of values present in Category column.

vr10 · February 28, 2023, 11:01am

I don’t Required Dynamic value In one sheet after getting fileter first Premier Support Calculation
Next sheet Development/Recovery Services calculation
Both are different so Input Data i am getting same sheet but i have filter these 2 .

Did you not receive the output as expected ?: i am getting perfect Answer what i am telling In Sample file “Mobile” “Services” name is there but in original file name should “Services” “Premier Support” so i can’t change its variable so i am asking that’s it apart from everything working Fine.

these 2 are fixed for calculation present:
“Services” “Premier Support”
“Services-Deploymeny/RecoveryServices.”

95% Everything working fine just for name for variable(“Mobile” -“Services” )instead of this i have to add these 2:I have to add that’s it
“Services” “Premier Support”
“Services-Deploymeny/RecoveryServices.”

I hope Your understood

Thanks

supermanPunch · February 28, 2023, 11:12am

@vr10 , If the values are fixed, then you could replace the first step :

supermanPunch:

After adding all the Data columns, we would need to use a For Each Activity and get the unique values present in the column Category (Premier support, Deployment services). For that purpose, we could use the Below Expression in the For Each Activity.
DT.AsEnumerable.Where(Function(x)Not(String.IsNullOrWhiteSpace(x("Category").ToString) Or x("Category").

Instead of that Expression provided you would just need to use the Values of Category as an Array.

{"Premier Support","Deployment/Recovery Services"}

The For Each Activity value would be changed in the below way :

vr10 · February 28, 2023, 12:16pm

Hi,

As per your guidelinnes i given

Inputfile :

Outputfile like this :

Not getting answer.

Thanks

supermanPunch · February 28, 2023, 12:57pm

@vr10 ,

Check the below workflow :
Excel_Calculate_ColumnValues.zip (791.6 KB)

Check the Input, Then run the workflow. An output file should be generated. Check if that is the Output that is needed for the input provided.

vr10 · February 28, 2023, 2:50pm

Thanks a lot for replying now working Everything is perfect.

system · March 3, 2023, 2:50pm

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

Topic		Replies	Views
How to add a same data to full column to the datatable Help	3	777	June 27, 2019
Append data table variable into existing recent excel file which having data into it Studio	13	1594	March 23, 2020
Writing variables to a datatable and appending datatable to excel Help	2	2520	January 31, 2019
How to append the data into excel using Add Data row activity Activities datatable , excel , activities , question	14	4053	March 24, 2022
Updating a Data Table - output from an Excel file Activities excel , activities , question	6	1659	February 28, 2021

Most Active Users - Yesterday
Yoichi
ashokkarale
Shreya_Gandhi
Anil_G
mjoseph74
Vinit_Mhatre
Ankit_Kumar2
Youri98
dutta.marina
adi.mehare
More details...

Adding the data from (stored variable )- data table to append the Excel sheet

Related topics