Count son by father

[#1]

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 !

[#2]

can you please upload example data, picture or something.

[#3]

Try this:

dimension: fatherID

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

[#4]

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

[#5 · Accepted solution]

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

[sushil353]

hi

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

[#7]

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 ?