Group by and count doesn't work

[Anonymous]

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!

[sasikanth]

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  ;

[Anonymous]

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?

[jonathandienst · Accepted solution]

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)