Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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
you use distinct
see attach pls
are you sure you need the distinct keyword in your count?
what answers are you getting that suggest it is wrong?
remove Distinct keyword
or see attach
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!
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!
It is working!
Can you share your app ?
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;