topic Count son by father in QlikView

Count son by father

Thu, 14 Apr 2011 15:00:53 GMT

Hello ! (sorry for my bad english ...)

I want to create a board with 2 columns :
- the father ID
- and the number of son' IDs

I don't know how make this expression.

Have you any function ? Or any idea ?

Thanks a lot for help !

Count son by father

Thu, 14 Apr 2011 15:09:56 GMT

can you please upload example data, picture or something.

Count son by father

Thu, 14 Apr 2011 15:10:45 GMT

Try this:

dimension: fatherID

expression: aggr(count(sonID),fatherID), or i think just count(sonID) will work also

Count son by father

Thu, 14 Apr 2011 15:18:57 GMT

Sorry, i miss an exemple ...

Father_ID ; Son_ID
1 ; null
2 ; null
3 ; 1
4 ; null
5 ; 2
6 ; 1

So i would like this result :
ID ; Number of son
1 ; 2
2 ; 1
3 ; 0
4 ; 0
5 ; 0
6 ; 0

Count son by father

Fri, 15 Apr 2011 11:03:29 GMT

I wrote the SQL query which return good result :

SELECT a.father, (SELECT count(*) FROM table1 b WHERE a.father=b.son) as Nb_son
FROM table1 a
GROUP BY a.father ;

But ... for the qlikview expression ... i'm really lost.

Thanks for help

Count son by father

sushil353 — Fri, 15 Apr 2011 11:08:17 GMT

as you have wrote some query to calcualte no of son... so put that query in load script and make a new field as a.father as father_id...

Try this..i think it would help you

Count son by father

Fri, 15 Apr 2011 13:50:54 GMT

I can put my query in the script, I know, but I should have to know if it was possible to give the same result with an expression in a board ?