word count using excel

I have a simple query & new to KNIME. I am able to read single excel file using XLS reader. I am basically have something like this for input from an excel sheet with 2 columns:

Excel Input:

Ticket  Problem

1          Memory

2          Disk

3          Controller

4          Disk

5          Memory

6          Disk

Expect output:

Problem category     Ticket Count

Memory                     2

Disk                           3

Controller                   1   

I read many forums and it was mentioned to use String to Document node after reader in order to proceed.  I fail to understand why we need to use "String to Document" in order to use other node fuctions. Is this how KNIME is designed to work only if things are in a form of Document? Please help with a sample workflow to get the expected output.

Regards,

Salz

 

I was able to figure it out. Thank you for the wealth of examples & advices in the KNIME forums that helped me get the solution.

I have another query about how to roll up subitems into main item with count added. Here is what I have.

Main item      Sub item       Count

Controller      Prob 1              5

Controller      Prob 2              3

Controller      Prob 3              6

Disk              Prob 4              9

Disk              Prob 5              8

PSU              Prob 6              2 

PSU              Prob 7              1

I am trying to get the following output:

Controller    14

Prob 1          5

Prob 2          3

Prob 3          6

Disk            17

Prob 4          9

Prob 5          8

PSU             3

Prob 6          2

Prob 7           1

I thought pivoting will help but I dont think that is the node for me. Kindly help me.

Regards,

Salz - KNIME learner

 

Hi Salz,

the 'XLS Reader'-node returns a data table (like your example input table) and there is no need to turn it into a document. You essentially need to make use of the 'GroupBy'-node in order to create the desired output (I attached a very small example workflow for simplicity).

Strings (i.e. text) need usually to be turned into 'documents' if a text analysis (i.e. text mining) should be carried out subsequently.

Best,

Martin

Hi Salz,

as far as I see are these two group by operations and a concatenate. 1. GroupBy node by Main item (sum of count) 2. GroupBy node by Sub Item (sum of count) 3. concatenate the two tables.

I assume you want to have the tables sorted, in a a way that the sub items follow the associated main item? You could do this by adding as aggregation of 2. (first of Main Item) and finally sort the table by that.

Does that help?

Cheers, Kilian