Count all occurrences of two string columns for cross table view

Hello everyone,
I have the following problem that I simply cannot solve:

Country Content type
USA News
Canada Blog
China News
Canada News
USA Blog
USA Images

I want to display the number of occurrences for the two columns in a crosstab like as follows:

News Blog Images
USA 1 1 1
Canada 1 1 0
China 1 0 0

(E. g. 20 entries for USA with content type = Blog)

I tried unique concat with count, group by, column aggregation but I seem to miss something as I’m not able to get both columns counted.
Thanks!

Hello @PeoplesAnywhere , and welcome to the KNIME forum.

This should be a simple task:

  1. You need to duplicate $Content type$ column. You can achieve this task with the help i.e. with a ‘String Manipulation’ node; join() or string()
  2. Pivot() $Content type$ and $new column$ as Manual Aggregation with Count(). There’s a ‘retain row order’ option in config.
  3. ‘Missing Value’ node aiming to replace missing with zeroes.

Let us know if further advise is needed.

BR

1 Like

Hi @gonhaddock and thanks for your quick response!

With your help I now get this:

ID Content-Type=News Content-Type=Blog Content-Type=Images Content-Type=…
Row0 234 321 54 0

It’s missing the countries.

I might done something wrong at 2.)
Pivot-Node with tab “Pivots” > Pivot columns = $Content-type$
and in the same Pivot-Node at tab “Manuel Aggregation” > Column $new_column$ with Count()

I think I might have found the solution myself.
I forgot to add $Countries$ within the Pivot-Node at tab “Groups”

With this enabled I now have what I was searching for. Thank you so much!

1 Like

Okey dokey @PeoplesAnywhere
I just forgot to comment that:

In ‘Pivot’ node’s Manual Aggregation >> Advanced settings there’s the option to keep ‘Pivot name’ and ‘Keep original name(s)’ for column naming.

BR

1 Like

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