topic Re: Group by in QlikView

Group by

Thu, 22 May 2014 09:37:27 GMT

Hi All,

Can any one help me with the below.

I have country and Date dimension and a fact.

I want to select country and respective max date.

e.g.: AUS May 01- 2014

AUS May 27 2014.

I want to display country and max(date) for that country in dashboard. I have used MAX(Date). But having multiple entries for the same country.

Can any one tell, how to retain one entry per country.

Thanks in advance.

Re: Group by

Thu, 22 May 2014 09:40:06 GMT

could you give us a screenshort for what you are trying to achieve

Re: Group by

hic — Thu, 22 May 2014 09:42:03 GMT

If you want just one entry per country, you should not have Date as dimension. Just create a chart with country as the only dimension and Max(Date) as expression.

HIC

Re: Group by

Thu, 22 May 2014 09:45:51 GMT

I want to have distinct max date values per country.

DIM: Country , Date

FACT : country_cd,date_key, Measure

I want to display all the countries present in fact with max(date)

Re: Group by

Thu, 22 May 2014 09:49:41 GMT

or if you need to use it as dimension you can use a calculated dimension and you wirte there:

aggr(max(Date),Date)

Hope this helps.

regards,

Re: Group by

tresB — Thu, 22 May 2014 09:54:51 GMT

This could be because of using improper way to calculate Date field at the script. Use Date() to calculate the field, like:

Load

Date(DateField) as Date

Re: Group by

Thu, 22 May 2014 11:30:56 GMT

Problem I am facing is I am getting two entries per country. Want to group by country.

I want to do something like this.

Select Country, Max(Date)

Group by Country

Result : AUS - 6/27/2014

Re: Group by

Thu, 22 May 2014 11:31:14 GMT

I am using Table box.

Re: Group by

Thu, 22 May 2014 11:35:18 GMT

for your needs you cannot use a table box it will just show you every entry you have in the table you are looking at. in you case i would suggest you a pivot table and then you can use for Date a calculated dimension with the expression

aggr(max(Date), Date) or you use Date as an expression instead of a Dimension and you jsut wirte max(Date)

I hope this helps.

regards,

Re: Group by

Thu, 22 May 2014 11:39:28 GMT

you should not use Date as dimension field if u want the max(date) and only 1 row.As Date is in dimension it wil give you all the values.

Re: Group by

Thu, 22 May 2014 11:42:29 GMT

it depends on what she is trying to achieve. if she wants the expressions only for the max(date) calculated then she has to use it as a dimension. if she just wants to see the max date then she should use it as and expression

Re: Group by

hic — Thu, 22 May 2014 11:49:40 GMT

No, the solution should not have date as dimension. One value per country was the requirement. That means Country as dimension and Max(Date) as expression. Nothing more is needed.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/25/dimensions-and-measures

HIC

Re: Group by

Thu, 22 May 2014 11:53:15 GMT

hey,

you could try aggr(max(date),country)

Re: Group by

hic — Thu, 22 May 2014 12:02:40 GMT

Aggr() is in this case an unnecessary complication that takes longer to evaluate. Why use it when you don't need it? Further, if you want it as expression, you need to wrap it in an aggregation function to make it work.

I suggest your read

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-used

http://community.qlik.com/blogs/qlikviewdesignblog/2014/05/19/function-classes

HIC

Re: Group by

saikatghosh — Thu, 22 May 2014 12:24:26 GMT

Hi Avinash,

if you want to group by on script level then your script should be like this. and in table view select MAXDATE along with Country.

Table1:

Load
COUNTRY,
Date(MAX(DATE),'DD-MM-YYYY') as MAXDATE
Resident MainTable; //Earlier Table Name
Group by COUNTRY;

Re: Group by

davidg_curs — Thu, 22 May 2014 17:30:52 GMT

Odsoten sem do 23.05.2014.

Odsoten sem 22.5.2014. Vaše sporočilo bom prebral in nanj po potrebi

odgovoril, ko se vrnem, to je 23.05.2014.

V nujnem primeru v zvezi z QLIKVIEW aplikacijami pa se obrnite na

g.Tekavčič Staneta oz. g.Oven Alojza.

Lep pozdrav

David

Opomba: To je samodejni odgovor na vaše sporočilo "Re: -

Group by" poslano naprej 22.5.2014 13:35:58.

To je edino sporočilo, ki ga boste prejeli, ko je oseba odsotna.

Re: Group by

davidg_curs — Thu, 22 May 2014 17:30:53 GMT

Odsoten sem do 23.05.2014.

Odsoten sem 22.5.2014. Vaše sporočilo bom prebral in nanj po potrebi

odgovoril, ko se vrnem, to je 23.05.2014.

V nujnem primeru v zvezi z QLIKVIEW aplikacijami pa se obrnite na

g.Tekavčič Staneta oz. g.Oven Alojza.

Lep pozdrav

David

Opomba: To je samodejni odgovor na vaše sporočilo "Re: -

Group by" poslano naprej 22.5.2014 14:24:53.

To je edino sporočilo, ki ga boste prejeli, ko je oseba odsotna.

Re: Group by

davidg_curs — Thu, 22 May 2014 17:30:53 GMT

Odsoten sem do 23.05.2014.

Odsoten sem 22.5.2014. Vaše sporočilo bom prebral in nanj po potrebi

odgovoril, ko se vrnem, to je 23.05.2014.

V nujnem primeru v zvezi z QLIKVIEW aplikacijami pa se obrnite na

g.Tekavčič Staneta oz. g.Oven Alojza.

Lep pozdrav

David

Opomba: To je samodejni odgovor na vaše sporočilo "Re: -

Group by" poslano naprej 22.5.2014 13:31:51.

To je edino sporočilo, ki ga boste prejeli, ko je oseba odsotna.

Re: Group by

davidg_curs — Thu, 22 May 2014 17:30:53 GMT

Odsoten sem do 23.05.2014.

Odsoten sem 22.5.2014. Vaše sporočilo bom prebral in nanj po potrebi

odgovoril, ko se vrnem, to je 23.05.2014.

V nujnem primeru v zvezi z QLIKVIEW aplikacijami pa se obrnite na

g.Tekavčič Staneta oz. g.Oven Alojza.

Lep pozdrav

David

Opomba: To je samodejni odgovor na vaše sporočilo "Re: -

Group by" poslano naprej 22.5.2014 13:39:50.

To je edino sporočilo, ki ga boste prejeli, ko je oseba odsotna.

Re: Group by

davidg_curs — Thu, 22 May 2014 17:30:53 GMT

Odsoten sem do 23.05.2014.

Odsoten sem 22.5.2014. Vaše sporočilo bom prebral in nanj po potrebi

odgovoril, ko se vrnem, to je 23.05.2014.

V nujnem primeru v zvezi z QLIKVIEW aplikacijami pa se obrnite na

g.Tekavčič Staneta oz. g.Oven Alojza.

Lep pozdrav

David

Opomba: To je samodejni odgovor na vaše sporočilo "Re: -

Group by" poslano naprej 22.5.2014 13:31:57.

To je edino sporočilo, ki ga boste prejeli, ko je oseba odsotna.