Calculated fields - Invalid formula with CSV data

Question

Hi,I'm trying to debug an issue with PivotView, which consists of the following: when we load data from a CSV and the data in the column or row headers is numeric, the calculated fields do not work and we always get the "Invalid Formula" error (please see screenshot below).The formula that is generated automatically when we drag and drop fields to the formula also doesn't seem to be correct.I've read your documentation and also looked up other threads here and issues on GitHub that report the same problem but, so far, I didn't find anything useful.I think the issue might be connected with a conversion of the header string into a number, which inevitably causes the invalid formula issue. I've tried to fix the formula by removing the quotation marks (for example: Sum(1969)) but then the calculated field column doesn't include any values.I've also tried the experiment with a field that has a string header and, in that case, the calculated fields feature seems to work properly.This is not an isolated incident related to a specific CSV file, since my team and I tried different CSV files as datasource and we had the same issue with every single file that had numeric headers instead of string headers.I attach the CSV file and the little code example that I'm using to debug the issue.Is there a way to solve this issue? Thanks in advance.Attachment: sample_e06943a8.zip

Saranya Sivan · Answer

Hi Oliveira, 
  
We considered “Issue in the calculated field creation” as a bug and logged a defect report in our data base. And it will be available in weekly patch release which is estimated to be rolled in the end of April, 2021. 
  
You can track the task in the following link. 
  
https://www.syncfusion.com/feedback/22944/issue-in-the-calculated-field-creation 
  
We appreciate your patience until then. 
  
Regards, 
Saranya Sivan.

M Oliveira · Answer

I've tried the calculated fields again using a different CSV file and I'm now getting the same "Invalid Formula" error in columns with String headers.

Here's an example:

If I use the "S-x" field to create a calculated field, I get a formula with a lot of trailing spaces (please see screenshot below).

Correcting the formula to "Count(S-x)" allows me to save the calculated field, but the values are empty. I've tried the same experiment for the Age field and I get both the poorly formed formula (and the Invalid Formula error), only to have to correct the calculated field formula and get another column without any values.

However, this issue doesn't happen with every field. For example, if I use the field Name, the formula is generated correctly and I get a column with correct values (please see below screenshot).

The code I used in these tests is the same in the sample attached to the original post. The CSV file I used for this particular test is attached here.

Is this a known bug?

Attachment: dataset_163e846e.zip

M Oliveira · Answer

Hi Saranya.Apologies. I didn't see your reply as I was writing the 2nd post reporting more details about the issue.Thanks for letting me know about the bug. I'll pass the information along to my team and we'll continue to monitor future updates.

Saranya Sivan · Answer

Hi Oliveira, 
 
We are checking on the reported issue at our end. We will update the further details within two business days(5 th March 2021). 
 
Regards, 
Saranya.

Saranya Sivan · Answer

Hi Oliveira,  
Please find the response below,  
We have checked the reported problem at our end. We found that there is white space for the field in the CSV file. To avoid this issue kindly remove the white space as mentioned in the below code snippet.  
Meanwhile, we have modified the sample for your reference. Please find the sample below,  
Code example:  




const input = document.querySelector('input[type="file"]'); 
input.addEventListener("change", function(e) { 
  const reader = new FileReader(); 
  reader.onload = function() { 
    const lines = reader.result.split("
").map(function(line) { 
      line = line.split(" ").join(""); 
      return line.split(","); 
    });  
    
Sample link:   https://stackblitz.com/edit/lacxea-a8sf6g?file=index.js 
 
Regards, 
Saranya Sivan.

M Oliveira · Answer

Hi Saranaya,I've tried the sample code you linked, but the issue with the invalid formulas persists even after removing the whitespaces. The dataset I used was the biostats.csv file I linked in my sample of the 2nd of March. I didn't change anything in your code sample. If you have any other suggestions, please let me know.Best wishes,M. Oliveira.

Saranya Sivan · Answer

Hi Oliveira,  
 
We have checked the reported problem at our end. We found that there is “” double quotes in the provided CSV file. To avoid this issue kindly remove the “” double quotes. 
And, also we have logged this (“Issue in the calculated field creation”) as a bug and logged a defect report in our data base. And it will be available in weekly patch release which is estimated to be rolled in the end of April, 2021.  
   
You can track the task in the following link.  
   
https://www.syncfusion.com/feedback/22944/issue-in-the-calculated-field-creation  
   
We appreciate your patience until then.  
   
Regards,  
Saranya Sivan.

M Oliveira · Answer

Hi Saranaya,I manually removed the double quotes from the test datasets and that seems to have helped in getting some calculated fields to work.There are also some other cases that return columns without values. This happens for CSV headers that have extra parenthesis.For example, in the biostats dataset, if I try to make a calculated field for Height (in),and this will result in a new column with no values:For these tests I used the sample of code you provided here:https://stackblitz.com/edit/lacxea-a8sf6g?file=index.jsand the datasets I attached to this reply.Manually removing the whitespaces and double quotes seems to have solved the issue with numeric headers. I'm now getting correct results for calculated fields that use numeric headers. However, if I try to do this programmatically, I still get the same Invalid Formula errors.This ensures that formulas similar to "Sum(1978)" will be generated as "Sum("1978")", which is of course invalid. I suppose this is connected to the bug report you added when I first reported these issues.I hope this information is useful for possible debug on your side.Attachment: datasets_2fa41541.zip

Saranya Sivan · Answer

Hi Oliveira,   
  
We have checked the reported problem at our end. We found that there is ( parenthesis symbol in the provided CSV file. To avoid this issue kindly remove the ( parenthesis symbol and set the caption as per your requirement.  
Meanwhile, we have modified the sample for your reference. Please find the sample below,   
  
Sample: https://stackblitz.com/edit/lacxea-5ashuk?file=index.js 
  
Code example:   




 fieldMapping: [ 
          { name: "Height", caption: "Height(in)" }, 
          { name: "Weight", caption: "Weight(lbs)" } 
        ],  
  
And, also we have logged this (“Values of Calculated field gets empty”) as a bug and logged a defect report in our data base. And it will be available in weekly patch release which is estimated to be rolled in the end of April, 2021.   
    
You can track the task in the following link.   
    
https://www.syncfusion.com/forums/163080 
    
We appreciate your patience until then.   
    
Regards,   
Saranya Sivan.

Manikandan Murugesan · Answer

Hi Oliveira, 
 
 
We are glad to inform you that the fix for the issue “Values of Calculated field gets empty” has been included in the weekly release (v19.1.0.56). Meanwhile we have prepared a sample in this version for your reference which is available in below link. 
 
Sample: https://stackblitz.com/edit/react-qkbxaw?file=index.js 
 
 
Please let us know if you have any concerns. 
 
Regards, 
Manikandan.