Group all columns dynamically

Thomas commented on Sep 30, 2020

Hi,

I have a data source that have the following column (just a part) names out of my mysql database from a survey: id submitdate lastpage startlanguage seed 201005X5X27 201005X5X28 201005X5X29 201005X5X30 201005X5X31 201005X5X32 201005X1X1a 201005X1X1b 201005X1X2a 201005X1X2b 201005X1X3a 201005X1X3b 201005X1X25a 201005X1X25b

I would like to have an average and a count from that column. This would be possible be the Group Process. Because I want to use this report not only on this survey but others too, the column name will change.

I've tried to save the column names in a global variable but it doesn't work. At least not in the setup().

->pipe(new CalculatedColumn(array(
            "qgid"=>function($row){
                global $save_data;
                $save_data[] = $row["c0"];
            }
        )))
->pipe(new Group(array(
          "count"=> $save_data,     
        )))

Is there an other way to achieve that?

PS: Of Course I meant "Group all columns" in the title. Can't change it.

David Winterburn commented on Sep 30, 2020

Hi Thomas,

To get all column names from a table inside a report's setup you could use the following code:

//MyReport.php
function setup()
{
    $this->src("mysql")
    ->query("select * from myTable limit 1") //we only need one row
    ->pipe($this->dataStore("temp"))
    ->requestDataSending(); //this command makes the report start this data pipe running right away

    $data = $this->dataStore("temp")->data();
    $row0 = $data[0];
    $columnNames = array_keys($row0);

    $this->src("mysql")
    ->query("select * from myTable")
    ...//$columnNames can be used in processes like Group, Cube, etc here

}

Please try this and let us know if there's any problem. Thanks!

Thomas commented on Sep 30, 2020

Great. Thank you David. The array was successfully saved in the variable and gave me the right selection.

Now that this is working I have kind of an error at the grouping process. After I filter the data, the grouping process ignores the filter.

$survey_data_group_2
        ->pipe(new Transpose2())
        ->pipe(new Filter(array(
            array("c0","endWith","a"),
            array("lastpage","=","5"),
        )))
        ->pipe(new Transpose2())
        ->pipe(new RemoveColumn(array(
            "c0"
        )))
        ->pipe(new Group(array(
            "count"=>$columnNames
        )))
        ->pipe($this->dataStore('data3'));

Grouping by "count" gives me the result "19" also it should be 5 after the filtering.

Is it possible to specify the Grouping? E.g. I need later the following fields out of the data table: "average of only the answered fields", "count of answered (not empty) fields", "count of not answered (empty) fields"

Is there a better way to do this?

Should I open a new Topic for this cases or just reply in the current discussion?

David Winterburn commented on Oct 1, 2020

Hi Thomas,

It's better to open a new topic when the problem changes. I also notice you didn't have the "by" property for Group process:

https://www.koolreport.com/docs/processes/group/

KoolReport's Forum

Group all columns dynamically #1637

Build Your Excellent Data Report