Pivoting and Boolean and Collection Methods

KNIME Analytics Platform
1

Hello, All. 

This is my first post here. 

I´m trying Knine for a while and have a queston: when I add the pivot node, in the description tab, there is references to Boolean and Collection Methods as well asGeneral and Numerical. 

However, tab Options only shows  General and Numerical Mehods when I right-click the column I want to aggregate. 

Any suggestion will be welcome. 

Best regards, 

Sergio

 

 

2

I believe these missing options you mention are only applicable on collection columns. I.e. These are columns where you have multiple values per cell.

you can create a collection column using the Create collection column node, or use the groupby or column aggregator node and aggregate by set or list, or even using the cell combiner node with create collection column set.

hope this helps

simon.

3

Hello, Simon. 

Thanks for your reply.

Let me explain what I need and you may be able to give me some directions, if possible. 

My goal is to use the association ruler. As far as I understand I need to have one of these two scenarios: 

  1. A table with the transactionID in the rows and columns with the items in each transaction plus 0/1 boolean number in the cells to determine which item is in which transaction. This is what I believe we call a bitvector table.
  2. A table with the transactionID and one collumn with the collection of the items per transaction. In this case, any transactionID should be unique

What I have is the second case, that shows something like:

(Table A)

TransactionID Itens
1 [A,B]
2 [A,C]

I got this table by grouping a table that shows:

(Table B)

TransactionID Itens
1 A
1 B
2 A
2 C

However, anytime a run the Assotiation Ruler on table A, I get an empty table as result. Using the same table B, I could get to find many relations on SQL Analyze Services, what indicates that it should work on Knine. BTW, we are talking about millions of records.

So, my initial question was an attempt to find out how to transform table B in the scenary exposed on item 1 in this message, i.e., a bitvector result. 

Any suggestion will be more than welcome. 

Thanks again, 

Sergio

 

 

4

If I understand correctly, you want a table with these columns;

transactionId, A, B, C, D, E etc...

and under A, B, C etc is a 0 or 1 if it is present.

To do this, take table B and use the One2Many node on the Items column.

now use the GroupBy node and choose to Group by TransactionId, under the options tab, add all columns, and choose to aggregate each by Sum.

hope that's what you're looking for.

simon,

5

Simon, I must have been missing something very simple. 

Please find attached a excerpt of table B.

One2Many node shows no other options than what is shown on attached file One2Many.png

One2Many node shows the following:

WARN      One2Many                           column: TIPOPRODUTO has no possible values

Any suggestion?

Thanks in advance!

Best regards, 

Sergio

 

 

6

Simon, thanks again. 

I must have been doing something wrong, Please see the attachments (tableB and One2Many.pgn) to see the real data. 

One2Many node throws: 

WARN      One2Many                           column: TIPOPRODUTO has no possible values

 

Any ideas?

 

Regards!

7

Hello, Simon.

When I try to One2Many from table B I get a "WARN      One2Many                           column: TIPOPRODUTO has no possible values" error. Tried to use a domain calculator before the one2many node with no success (same error when trying to one2many. 

Any suggestions?

Regards

Sergio

8

Hi I am unsure what the issue is, as if table B looks like the format you show in your post, where the items column contains numerous string entries, then One2Many should split these out into individual columns.

normally any issues would be resolved by the Domain Calculator node which you have already tried. If you have lots of possible values in this column, make sure the domain limits within this node are set high enough.

simon.