topic Re: Group by and count doesn't work in QlikView

Group by and count doesn't work

Anonymous — Fri, 16 Sep 2016 08:12:43 GMT

Hi all,

i have the following problem and don’t get the answer – even it should be very easy:

I have this table:

Alter	ID
4	23432423
3	234234234
2	234234
4	234234234
3	234234234
2	234234234
1	324234234

Now I want to group by “Alter” and count the number of IDs.

Result should be:

Alter	Counted ID
1	1
2	2
3	2
4	2

Warteschlange:
LOAD Distinct
Alter,
count(distinct ID) as Anzahl_Warteschlange
Resident Datenload_Datum
Group by Alter;

The problem is, that QlikView doesn’t do the group by.

Can you help?

Thank u very much!

Re: Group by and count doesn't work

sasikanth — Fri, 16 Sep 2016 08:24:18 GMT

HI ,

It working for me, but your output is wrong

For Alert 3 you will get Count =1 only (bcz both IDs are same)

PFA

Re: Group by and count doesn't work

florentina_doga — Fri, 16 Sep 2016 08:25:38 GMT

you use distinct

see attach pls

Re: Group by and count doesn't work

tracysmart — Fri, 16 Sep 2016 08:26:01 GMT

are you sure you need the distinct keyword in your count?

what answers are you getting that suggest it is wrong?

Re: Group by and count doesn't work

varshavig12 — Fri, 16 Sep 2016 08:26:19 GMT

remove Distinct keyword

Re: Group by and count doesn't work

florentina_doga — Fri, 16 Sep 2016 08:30:37 GMT

or see attach

Re: Group by and count doesn't work

Anonymous — Fri, 16 Sep 2016 08:39:47 GMT

Hi all,

thank you very much, but it doesn't work for me.

I always get this answer:

Alter	counted ID
4	1
3	1
2	1
4	1
3	1
2	1
1	1

Thank you very much!

Re: Group by and count doesn't work

Anonymous — Fri, 16 Sep 2016 08:47:08 GMT

Hi Sasi,

thank you very much, but it doesn't work for me.

I always get this answer:

Alter	counted ID
4	1
3	1
2	1
4	1
3	1
2	1
1	1

Thank you very much!

Re: Group by and count doesn't work

varshavig12 — Fri, 16 Sep 2016 08:47:37 GMT

It is working!

Can you share your app ?

Re: Group by and count doesn't work

Anonymous — Fri, 16 Sep 2016 08:54:00 GMT

Datenload_Datum:

LOAD

Year(Zeitstempel) as ZS_Jahr,

month(Zeitstempel) as ZS_Monat,

day(Zeitstempel) as ZS_Tag,

Time(Zeitstempel) as ZS_Uhrzeit,

Hour(Zeitstempel) as ZS_Stunde,

Interval(Now()-Startzeit,'hh') as Alter,

Resident Datenload;

drop Table Datenload;

Zwischenschritt:

LOAD

Alter as Alter2,

Proben_ID as Proben_ID2

Resident Datenload_Datum;

Warteschlange:

LOAD

Alter2,

count(Proben_ID2) as number

Resident Zwischenschritt

Group by Alter2;

DROP Table Zwischenschritt;

Re: Group by and count doesn't work

sasikanth — Fri, 16 Sep 2016 08:57:16 GMT

Why do you need 3rd resident load ,

you can do the same in 2nd , try this

wischenschritt:

LOAD

Alter

Count(distinct Proben_ID) as Number

Resident Datenload_Datum Group by Alter ;

Re: Group by and count doesn't work

Anonymous — Fri, 16 Sep 2016 09:51:03 GMT

Hi,

thank you all - Now I know the problem - but not the solution.

The Problem seems to be in the buidling of "Alter" by this function "Interval(Now()-Startzeit,'hh') as Alter".

That seems to be the reason why group by doens't work.

Startzeit is "2016-07-29 13:40:13.28" and it is different for all the values, but I want to get the answer from "Interval(Now()-Startzeit,'hh') in hours.

Can you help for this or any ideas?

Re: Group by and count doesn't work

jonathandienst — Fri, 16 Sep 2016 10:09:00 GMT

Interval() does not round the numbers to the nearest hour, it simply formats them. To round the values of the interval, you will need this:

Interval(Floor(Now() - Startzeit, 1/24), 'hh') as Alter,

(assuming Startzeit is a numeric date value (right aligned in a list box). If it is a string value, you will need:

Interval(Floor(Now() - Time#(Startzeit, 'dd-MM-yyyy hh:mm:ss.ff'), 1/24), 'hh') as Alter

(play around with the format string to match your date format. Better still, convert it to a date on load)