7 Replies Latest reply: Aug 16, 2018 1:42 PM by Jaydeep Paeikh RSS

    Simple Table and Group By

    Jaydeep Paeikh

      This is my first attempt at Qlik.

      I am trying to render a Table in Qlik Sense, where the contents are grouped on multiple columns.

      Do not want to do grouping during data load but instead use expression while defining dimensions/measures.

       

      Expected Table output-

       

      Field1     Field2                   Field3          Count

      O1      1/1/2018-01       V1             3
      O11/1/2018-02V15
      O11/1/2018-02V210
      O11/1/2018-03V17
      O11/1/2018-03V51
      O11/1/2018-03V62
      Total28

       

      Here Field1, Field3 are strings,

               Field2 is Timestamp (above table shows MM/DD/YYYY-HH), and

               Count is computed value based on following grouping-

                    - group by Field1, Field2, Field3, and distinct FieldX (not part of output)

                    - where Field1 = a given set of values, Field2 is in a given date range, and FieldY (not part of output) is certain value

       

      Right now, I am using Count() function with Set Analysis to filter out rows from input data (as described in where clause above).

      NOTE: Field1 is currently filtered during the data load.


      Count({$<FieldY={"2100*"},Field2={"<=$(=EndDate)>=$(=StartDate)"},Field3-={""}>}  DISTINCT FieldX),

       

      Below is the table output I am getting.

      Issue I am facing is, when the table is rendered-

        -  At times combination of Field2 and Field3 are getting repeated

        - At times Field3 shows incorrect value (row 4 below)

       

      Field1     Field2               Field3         Count

      O1      1/1/2018-01       V1             2
      O11/1/2018-01 V11
      O11/1/2018-02V15
      O11/1/2018-02V33
      O11/1/2018-02V27
      O11/1/2018-03V17
      O11/1/2018-03V51
      O11/1/2018-03V62
      Total28

       

      I have also tried to user Aggr() around Count but then the table shows lot blank Count values.

       

      Aggr(

      Count({$<FieldY={"2100*"},Field2={"<=$(=EndDate)>=$(=StartDate)"},Field3-={""}>}  DISTINCT FieldX),

      Field1,

      Field2,

      Field3

      )

       

      I also came across FirstSortedValue() function that can be used in combination with Aggr() as dimension expression. But when I use FirstSortedValue() on a dimension, it gives error 'Invalid Expression'.

       

      Please help me with your expertise to understand how to achieve the desired result.

      Thanks.

        • Re: Simple Table and Group By
          Juraj Misina

          Hi,

          I'm not excatly sure what you'd like to achieve but I can offer some pointers:

          - If Field2 is a timestamp, does it contain minute information? Although values on row 1 and 2 can be formatted to the same format, they still can be distinct values, hence two rows.

          - Your set analysis says

          Field3-={""}

          What exactly is this supposed to do? Based on row 4 of your expected table I assume you want to use

          Field3-={'V3'}


          Hope this helps.

          Juraj

            • Re: Simple Table and Group By
              Jaydeep Paeikh

              Thanks for your input.

               

              Field2 (timestamp) does have hours, minutes and seconds but I want to group by Hour.

              Field3 can not be blank, hence I am using Field3-={""}

              I believe, a minus before equal acts as 'not equal'.

               

              In terms of requirement, I am trying to get hourly count of a certain metric (FieldX) for a given - set of Field1, and given range of dates for Field2. While doing so, exclude any where Field3 is blank/null.

               

              Relationship between fields is-

                (Field1  One)-->(Many  Field2)

                (Field2  One)-->(Many  Field3) 

                (Field3 One)->(Many FieldX)