Simple Table and Group By

[Anonymous]

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
O1	1/1/2018-02	V1	5
O1	1/1/2018-02	V2	10
O1	1/1/2018-03	V1	7
O1	1/1/2018-03	V5	1
O1	1/1/2018-03	V6	2
Total			28

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
O1	1/1/2018-01	V1	1
O1	1/1/2018-02	V1	5
O1	1/1/2018-02	V3	3
O1	1/1/2018-02	V2	7
O1	1/1/2018-03	V1	7
O1	1/1/2018-03	V5	1
O1	1/1/2018-03	V6	2
Total			28

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.

[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

[Anonymous]

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)

[juraj_misina]

In that case I recommend either:

- round Field2 in script by Round (TimestampValue, 1/24) as Field2

- create Field2 as Date(Floor(TimestampValue))&'-'&Hour(TimestampValue) as Field2

TimestampValue being underlying value of your Field2 to get rid of minutes and seconds. This will solve the "duplicate" rows.

As for Field3, your assumption with minus sign is corret. Try adjusting the set analysis and replace double quotes with single quotes:

Field3-={''}

Double quotes denote search string (so you can use advanced search or wild cards), single quotes denote string literal, so the above actually means empty string.

Juraj

[dwforest]

For Field3, you can use Field3=

This means Field3 = to any value (null is not a value)

[juraj_misina]

This actually means "ignore selections in Field3" so would also include null. You probably ment Field3={"*"}.

[rangam_s]

use text(Field2) as dimension so that it will remove all the information which was not showing in the formatted one.

[Anonymous]

Created a column (Field2YYYYMMDDHH) with following expression to exclude miute and seconds from timestamp.

Text(Date(Date(Floor(Field3))+MakeTime( Hour(Field3)),'YYYY-MM-DD HH:mm:SS'))

Now when I use following expression on count column, there are lot of rows with blank count value-

Aggr(

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

Field1,

Field2YYYYMMDDHH,

Field3

)