Topic
9 replies Latest Post - ‏2013-11-18T13:45:32Z by mburati
VSinha
VSinha
11 Posts
ACCEPTED ANSWER

Pinned topic Group by for more than one DB column using Transform Group builder

‏2013-04-02T16:31:00Z |
Hi All,

I have requirement where I need to group by a table column for more than one columns. I am able to use transform group builder for one column without any issue.

Was wondering what is the approach and best practices for using the transform group builder for muliple DB columns. Idea is to use transform group builder instead of LJO, at first.

I am using WEF V8.0

Really appreciate any valuable inputs.

Thanks,
Vish
Updated on 2013-04-03T20:11:46Z at 2013-04-03T20:11:46Z by VSinha
  • kevintap
    kevintap
    745 Posts
    ACCEPTED ANSWER

    Re: Group by for more than one DB column using Transform Group builder

    ‏2013-04-02T17:10:43Z  in response to VSinha
    In order to group by more than one column, you can use the "Get Group Value Method" input to provide a custom method which can provide the grouping value to use. In this case, you would want to specify that the "Child Element to Group By" is the row element (not a child element of the row). Then your custom method will be passed the row element. It can then construct a unique grouping value by retrieving the text of multiple child elements of the row.

    An example custom method might look like this:
    
    
    { 
    
    return row.getText(
    "severity") + 
    ":" + row.getText(
    "priority"); 
    }
    


    I'm attaching a sample model which demonstrates this technique.
    • VSinha
      VSinha
      11 Posts
      ACCEPTED ANSWER

      Re: Group by for more than one DB column using Transform Group builder

      ‏2013-04-02T19:56:38Z  in response to kevintap
      Thanks for the response. This really helps!

      Actually, sample example Transform Group builder using getGroupValue method is creating a new element called "group_value" and this is combining the two output element in one.
      Ideally in my case I have to maintain two separate elements like below -

      <groups>
      <group>
      <severity>string</severity>
      <priority>string</priority>
      <row>
      <id>string</id>
      <description>string</description>
      <severity>string</severity>
      <priority>string</priority>
      </row>
      </group>
      </groups>

      Not sure if I can use within same Transform Group builder, or do you have any sample for this?

      Thanks again for all the help.
      • kevintap
        kevintap
        745 Posts
        ACCEPTED ANSWER

        Re: Group by for more than one DB column using Transform Group builder

        ‏2013-04-03T02:32:47Z  in response to VSinha
        Here is an updated version of the sample that I previously posted. This demonstrates 2 different techniques to get the data in the format that you desire.

        First, you can achieve this using a Transform Map builder. The Transform Map builder can examine the children (severity, priority) of the row element and copy them to the group element. The only disadvantage to using this method is that the Transform Map builder will copy the elements 'n' times where 'n' is the number of row elements under the group element. So your result may not strictly adhere to the schema.

        The second approach is to use a Transform Modify builder with a custom IXml method. The method called "split" in the sample simply splits the group_value element back into the severity and priority fields. The split method returns null which signals to the Transform Modify builder that the group_value element should be removed from the XML structure. Since the Transform Modify builder does not allow the schema to be changed, you must use a Transform Map builder to adjust the schema of the resulting data. The Transform Map builder in this case, just has a single mapping of the top level element with the copy children box checked.

        Let me know if you have any questions on these options.
        • VSinha
          VSinha
          11 Posts
          ACCEPTED ANSWER

          Re: Group by for more than one DB column using Transform Group builder

          ‏2013-04-03T16:00:35Z  in response to kevintap
          thanks again Kevin. appreciate your help. I will try fit your design in our app and see if I have any further question.

          Meanwhile if you can share some sample using Transform Group and Transform Aggregate builders that would be really great. I tried using other than count operation in Transform Aggregate like sum operation and was not getting the desirable output.

          Thanks,
          Vish
          • VSinha
            VSinha
            11 Posts
            ACCEPTED ANSWER

            Re: Group by for more than one DB column using Transform Group builder

            ‏2013-04-03T20:11:46Z  in response to VSinha
            Never mind, I was able to explore all different methods of Transform Aggregate builder options along with Transform group.

            thanks!
        • Rohini_poc
          Rohini_poc
          14 Posts
          ACCEPTED ANSWER

          Re: Group by for more than one DB column using Transform Group builder

          ‏2013-11-15T11:00:23Z  in response to kevintap

          Hi,

          We are using WEF 8.0.

          We have excel sheet data with two columns.We need to change color of first column depending on the value of another column.

          if first column > second column

          change color of first column to red

          else

          change color of first column to green

           

           

           

          • mburati
            mburati
            2551 Posts
            ACCEPTED ANSWER

            Re: Group by for more than one DB column using Transform Group builder

            ‏2013-11-15T12:59:26Z  in response to Rohini_poc

            This seems to be a new/unrelated question in an already marked-answered topic about Transform builder, so it probably should be in a new thread.

             

            In any case, assuming that you are displaying this data with Page Automation (ie, Data Page or higher level builder built on data page like V&F or DSUI) then as page automation generated code renders each row of the table, it'll save the contents of that row (temporarily) in its rowloop variable for that table/list.

            The first thing I would try is to use an Attribute Setter (single style) finding the page location of the cell that you want to set and set the style or class attribute on it to change the css style of that element appropriately.   The attribute setter "value" would be an indirect reference to a method that takes an IXml variable and you could pick into the variables to pass the rowloopvar for that list/table.   Then in that method you have a single row of the table (as IXml) where you can get the value of the two child elements (cells) compare them and return the appropriate style or class based on the results of that comparison.

             

            I hope that info helps,
            ..Mike Burati 
            The postings on this site are my own and do not necessarily represent the positions, strategies, or opinions of IBM.
            • Rohini_poc
              Rohini_poc
              14 Posts
              ACCEPTED ANSWER

              Re: Group by for more than one DB column using Transform Group builder

              ‏2013-11-18T12:49:12Z  in response to mburati

              Hi Mike,

              I have created one method where I am getting child elements and I am comparing those elements.

              But the probelem is I need to apply style to bars of a chart.I am not able to get each bar location on a page .

              Can you please tell me how to apply style to each row(as IXml) in java?

              I have written below code to get element values from XML schema.

              IXml valueOfMyXMLVariable = webAppAccess.getVariables().getXml("schema_name");

               

              XmlUtil xu = new XmlUtil();

               

               

              cnt=xu.countChildren(valueOfMyXMLVariable);

               

              for(int i=0;i<cnt;i++)

               

               

              {

               

               VariableTest=webAppAccess.getVariables().getXmlText("schema_name", "ExcelContent/Row["+i+"]/element_name");

              }

              Now need to apply style to these element.

               

              • mburati
                mburati
                2551 Posts
                ACCEPTED ANSWER

                Re: Group by for more than one DB column using Transform Group builder

                ‏2013-11-18T13:45:32Z  in response to Rohini_poc

                Sorry, I'm confused.   In the above thread you were asking how to apply a color style to a cell (column/row) based on a comparison with the next cell,   which is where I had suggested using an Attribute Setter.       Did you mean to post that follow up in your other thread about bar charts and bar colors?    If so, it sounds like the Jviews chart expert (Kevin) said that the JViews charts don't seem to support alternating bar color in a series like that, and you may have to create a custom chart using the chart designer.    If you can use the stacked chart option Kevin suggested in the other thread, that may help.

                In any case, while you could set a metadata element in the XML indicating whether something belonged to a green series or a red series, you're not applying a style to an XML element you're just adding information to the XML (styles are set on html).

                Walking XML with XPath containing an index would not be very efficient, as each of those XPath searches is going to start from the beginning so it's searching the tree multiple times.     Doing a getChildren and walking the results of that or getFirstChildElement and getNextSiblingElement until null would be more efficient.    

                If you want to create a new element or set an attribute, to track the comparison, you could then for each of those row elements get the string/text value of  colA and colB and compare them (where those column names are the two you want to compare).   Setting an attribute (colAelement.setAttribute("mystyle", "green");  would be easier, but in Kevin's other thread (for etting color in a stacked chart) I believe he based the series off an additional element,  so with the current row you could do something like row.addChildWithText("myseries", "green");

                Again, that's if Kevin's other thread with the stacked chart based on additional element solution works for your use case.     If not, then you may want to look into his suggestion of creating a custom chart type (I'm not a JViews chart expert so Kevin would have better advice on that front).

                 

                I hope that info helps,
                ..Mike Burati 
                The postings on this site are my own and do not necessarily represent the positions, strategies, or opinions of IBM.