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


      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


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



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






      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.


        • Re: Simple Table and Group By
          Juraj Misina


          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


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


          Hope this helps.


            • 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)