Knime Correct format

Hi!! Knime Expert and Enthusiastic…
Hope you are doing well.

I just want your help in creating / selecting appropriate format for column.

My issue:

I have attached herewith excel file with data(Sheet 1).
Where I am applying pivot to that column to find number of time its repetition, in excel (in sheet 2) I am getting unique number of rows = 7606.

Now I am doing same in Knime using group by, I am getting unique number of rows = 7483.

I don’t know how some cell changes its format, and its reduces it count to 7483.Please guide me to bring same output as of Excel Pivot.
Attached Excel File:
Power Series.xlsx (314.1 KB)

Hi @ravi13,

Check “Add this data to the data model” when inserting pivot table and then drag and drop “power series” into “Values” and in “Value field setting” > “Summarize value field by” select the last option which is “Distinct Count”. There are 7483 unique values in “Power Series”.

You can also get the same number of rows in pivot table if you select “Text” as the column type (format) for Power Series before inserting pivot table.

:blush:

3 Likes

Thanks @armingrudd

But basically, what I need to do as per client requirement is that I need to concatenate(combine as per Knime) this power series with another 2 column and need to found number of times each combination is repeating.

As they have done in excel, there output is coming as per 7606 count(as per Sheet-2 in previous shared excel) and I am doing that same process in knime as per 7483 count and because of this i am not able to match mine output with there output.
Please Guide.

You are using “General” format for the column. Change it to “Text” in excel and your pivot table will give you the same results as in KNIME. Currently, all values do not get aggregated so you have duplicate values in Excel.

:blush:

1 Like

Okay @armingrudd , but is there any changes i need to do in knime so i can match to 7606

Dear @ravi13,

The actual number of unique values in “Power Series” is 7483.

When you don’t add data to the data model, some values do not get aggregated correctly. You can test it by inserting another pivot table on the previous one. You will see that there are multiple duplicates.

I was wrong about changing cell format in Excel. It does not matter unless you check the “Add data to the data model” option when inserting pivot table.

7606 is not the true number of unique values.

:blush:

2 Likes

@armingrudd Thank you So much, I got it.
:slight_smile:

1 Like

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