Solved: Group by and count doesn't work - Qlik Community

Anonymous · ‎2016-09-16

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!

jonathandienst · ‎2016-09-16

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

sasikanth · ‎2016-09-16

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

florentina_doga · ‎2016-09-16

you use distinct

see attach pls

tracysmart · ‎2016-09-16

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

what answers are you getting that suggest it is wrong?

varshavig12 · ‎2016-09-16

remove Distinct keyword

florentina_doga · ‎2016-09-16

or see attach

Anonymous · ‎2016-09-16

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!

Anonymous · ‎2016-09-16

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!

varshavig12 · ‎2016-09-16

It is working!

Can you share your app ?

Anonymous · ‎2016-09-16

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;