Create a matrix view of string cells using cube?

Mark Mulder commented on Mar 4, 2021

Hi,

We are trying to create this view: Actorrole T12-011 T12-012 T12-013 A12-011 U U A12-001 ex in A12-013 in, ex

from this table:

Actorrole Link Transaction A12-011 U T12-011 A12-001 in T12-012 A12-013 in,ex T12-013 A12-011 U T12-012 A12-001 ex T12-011

with this code in the setup node: $query="SELECT * FROM joomla02.vw_bat";

    $node = $this->src("Test1")
    ->query($query);
   
    $node->pipe(new Cube(array(
        "row" => "Actorrole",
        "column" => "Transaction",
        "sum" => "Link"
    )))->saveTo($node2);
    $node2->pipe($this->dataStore('data'));

And sum gives only 0 in the matrix, but we need the string value. What needs to be changed to achieve this result?

David Winterburn commented on Mar 5, 2021

Hi Mark,

Did you want to concat the strings instead of performing sum on them?

Mark Mulder commented on Mar 5, 2021

Yes, so that the strings (in, ex, U) appear concatted in the matrix cell. The concatting can also be in the SQL, but then I'm still missing the replacement for 'sum' in the array command.

Mark Mulder commented on Mar 17, 2021

Can anyone look at this question again. Thanks in advance.

David Winterburn commented on Mar 18, 2021

Hi Mark,

The "sum" operator is only supposed to work with numeric values. In case you want to concatenate string values I would suggest using a custom aggregate like this:

    ->pipe(new Cube(array(
        "column" => ...,
        "row" => ...,
        "customAggregates" => array(
            "concat" => array(
                "{initValue}" => "",
                "{aggValue}" => function($aggValue, $value) {
                    return $aggValue . " - " . $value;
                }
            )
        ),
        "concat" => "Link",
    )

Pls try this and let us know if it works for your case. Thanks!

Mark Mulder commented on Mar 23, 2021

Hi David,

Thank you David, this works. I have a little additional question. Now every blank cell has a '0' in the cell instead of leaving it blank. I have tried to solve it using this topic: 'Multiple Data sources for 1 table #18' But I can't get it to work. Can you help me with this problem?

Sebastian Morales commented on Mar 26, 2021

Which widget did you use to display the data? I think both Table and DataTables has a property called "emptyValue" which could be set to show empty values. Tks,

Mark Mulder commented on Apr 1, 2021

Hi Sebastian,

Thank you for your answer. I couldn't get the 'emptyValue' working, but I finally got it fixed with the 'FillNull' from the 'cleandata' widget.

KoolReport's Forum

Create a matrix view of string cells using cube? #1943

Build Your Excellent Data Report