Group by with aggr()?

Sahal — Thu, 31 Mar 2016 09:56:26 GMT

Hi,

I have a table that looks like this:

User	ModifiedTime	ChangeType	Source Document	OUTPUT
po44	2016-03-31 08:46	Update	Test2.qvw	Created
po44	2016-03-31 08:46	Update	Test2.qvw	WhatEverChangetypColumnHave
po44	2016-03-31 09:00	Update	New QlikView Document.qvw	Created
po44	2016-03-31 09:10	Update	New QlikView Document.qvw	WhatEverChangetypColumnHave
po44	2016-03-31 09:22	Delete	New QlikView Document.qvw	WhatEverChangetypColumnHave
po44	2016-03-31 09:24	Delete	Test2.qvw	WhatEverChangetypColumnHave
po44	2016-03-31 09:31	Update	frame.qvw	Created
PO49	2016-03-31 08:51	Update	Test2.qvw	WhatEverChangetypColumnHave

I want to create a dimension column that looks like the OUTPUT Column.

As you can see i would like to use (if possible) the aggr() function or something within the chart that groups by sourcedocument
,user and modefiedtime.

when a new sourcedocument is added then "created" value will appere insted of "update" but this is only when there is a new sourcedocument added and that is why i think i need to group by because if you have a look at the last row for user PO49 the same sourcedocument appers but the document is already there and should not be "created".

I have playd around with the min() function for modified time but not sure exactly how to use with aggr().

Re: Group by with aggr()?

swuehl — Thu, 31 Mar 2016 10:14:17 GMT

Maybe create a chart like this

Dimensions:

Source Document

ModifiedTime

Sort chart by Source Document and ModifiedTime ascending.

As expression:

=If(Rowno() = 1, 'Created', Only(ChangeType) )

Other fields can be added as well to the expressions, if you need them to show.

Re: Group by with aggr()?

Sahal — Thu, 31 Mar 2016 11:10:49 GMT

Hi,

I got this to work:

if(isnull(Rank(Total Aggr(Sum(ModifiedTime), [Source Document]))),ChangeType,'Created')

Only sort by ModifiedTime Ascending

Thanks to this topic Row numbering by two dimension

topic Re: Group by with aggr()? in QlikView

Group by with aggr()?

Re: Group by with aggr()?

Re: Group by with aggr()?