Collection Manipulation

[MadhuG001]

Hi All,

Scenario:- I have some students record, with marks of 5 subject & I want to calculate total marks & percentage & to update in excel again.
I am moving my data from an excel to collection & then collection to a Work Queue.
So the Output of "Get Next Item" is stored in the Collection "Data"
But in the Calculation stage(Expression: [Data.English]+[Data.Hindi]+[Data.Math]), when I am trying to add marks in different subject, I am getting below error:-
"Internal : Failed to evaluate expression '[Data.English]+[Data.Hindi]+[Data.Math]' - Can't add to Text"

I am not sure why the Number Data types are getting pulled as Text

Please advise.
Thanks

 

[shikhar.mishra]

Hi Madhu,
I just checked with your sets of example on my work queue. its working fine.. I think you are getting error because your storing data on to text variable, please make new variable with datatype number and store result of [Data.English]+[Data.Hindi]+[Data.Math] expression.

Let me know if any other problem

 

[MadhuG001]

Hi Madhu,
I just checked with your sets of example on my work queue. its working fine.. I think you are getting error because your storing data on to text variable, please make new variable with datatype number and store result of [Data.English]+[Data.Hindi]+[Data.Math] expression.

Let me know if any other problem

Hello Shikhar,

Thanks for replying.
I didn't make any separate variable to store the values of different subject.
Individually it's getting stored in Collection "Data"

I am getting the result by altering the Collection Expression to "ToNumber([Data.English]) + ToNumber([Data.Hindi]) + ToNumber([Data.Math])"
But I am not sure, how to get the final results back to Excel file again?


If you have any idea, please share it.
Thank you.

 

[shikhar.mishra]

Please let me know how your excel look like and how you want to put total into excel . Surely I will give solution

 

[MadhuG001]

Please find the attachment.
My requirement is at the end of the process the Result & Percentage to be update in excel sheet.

Please let me know, if you found any solution.
Thank You

 

[Pavan3830]

Hi All,

Scenario:- I have some students record, with marks of 5 subject & I want to calculate total marks & percentage & to update in excel again.
I am moving my data from an excel to collection & then collection to a Work Queue.
So the Output of "Get Next Item" is stored in the Collection "Data"
But in the Calculation stage(Expression: [Data.English]+[Data.Hindi]+[Data.Math]), when I am trying to add marks in different subject, I am getting below error:-
"Internal : Failed to evaluate expression '[Data.English]+[Data.Hindi]+[Data.Math]' - Can't add to Text"

I am not sure why the Number Data types are getting pulled as Text

Please advise.
Thanks

hi , by default the data type in collection is text , to add text firstly we have to convert the text to number using converstion functions after that we can add......

 

[Abusufiyan Belief]

Please check first what is data type of Result & Percentage in collection.
Do store the result as Data.Result in one calculation stage and Data.Percentage in another(you can use multiple calculation stage as well), this will store the result in Data collection.

For store all in excel you must have to create an instance, open instance, save in worksheet from collection and then close an instance in action stages. Use Excel VBO utility for that.

 

[Amarender]

Hi All,

Scenario:- I have some students record, with marks of 5 subject & I want to calculate total marks & percentage & to update in excel again.
I am moving my data from an excel to collection & then collection to a Work Queue.
So the Output of "Get Next Item" is stored in the Collection "Data"
But in the Calculation stage(Expression: [Data.English]+[Data.Hindi]+[Data.Math]), when I am trying to add marks in different subject, I am getting below error:-
"Internal : Failed to evaluate expression '[Data.English]+[Data.Hindi]+[Data.Math]' - Can't add to Text"

I am not sure why the Number Data types are getting pulled as Text

Please advise.
Thanks

Hi Madhu

Update your Expression as "To Number([Data.English])+To Number([Data.Hindi])+To Number([Data.Math])". This is because after getting your data from Excel to collection the total "DATA-TYPE" for all the data will be "TEXT" you need to convert that data to number again.

This is done using the "calculation stage>Functions>Conversions>To Number[Your Data]" and store the result SUM in "Data Item>Data-Type> Number".

After Storing the Result data (SUM) in "Data item - Number" you perform the "Percentage Calculation"

Hope you Understand and help you on your Issue

Thanks and regards
AMAR

 

[MadhuG001]

Hi Madhu

Update your Expression as "To Number([Data.English])+To Number([Data.Hindi])+To Number([Data.Math])". This is because after getting your data from Excel to collection the total "DATA-TYPE" for all the data will be "TEXT" you need to convert that data to number again.

This is done using the "calculation stage>Functions>Conversions>To Number[Your Data]" and store the result SUM in "Data Item>Data-Type> Number".

After Storing the Result data (SUM) in "Data item - Number" you perform the "Percentage Calculation"

Hope you Understand and help you on your Issue

Thanks and regards
AMAR

Hello Amar,
Thanks for replying back.
As I mentioned earlier, I am getting the result(SUM & PERCENTAGE) into collection.

But I am not sure, how to update the result again to the excel file ?
Please let me know, if you have any idea !!

 

[MadhuG001]

Please check first what is data type of Result & Percentage in collection.
Do store the result as Data.Result in one calculation stage and Data.Percentage in another(you can use multiple calculation stage as well), this will store the result in Data collection.

For store all in excel you must have to create an instance, open instance, save in worksheet from collection and then close an instance in action stages. Use Excel VBO utility for that.

Thanks Abusufiyan Belief
I will try this approach.

 

[Amarender]

Hello Amar,
Thanks for replying back.
As I mentioned earlier, I am getting the result(SUM & PERCENTAGE) into collection.

But I am not sure, how to update the result again to the excel file ?
Please let me know, if you have any idea !!

Hi Madhu

Their is no direct method to update directly into excel.

But if you want to update in the same excel file, then use the output collection as input and using MS Excel VBO>write collection to excel which will update the old data with new data (including the sum and percentage) by replacing.

If you want to write data to new excel file then MS Excel VBO>create instance>create work book>create worksheet>write collection>save current workbook>close current workbook

Hope you understand and helpful

Thanks and Regards
Amar

 

[MadhuG001]

Hi Madhu

Their is no direct method to update directly into excel.

But if you want to update in the same excel file, then use the output collection as input and using MS Excel VBO>write collection to excel which will update the old data with new data (including the sum and percentage) by replacing.

If you want to write data to new excel file then MS Excel VBO>create instance>create work book>create worksheet>write collection>save current workbook>close current workbook

Hope you understand and helpful

Thanks and Regards
Amar

Thanks Amar for the quick response.