3 Replies Latest reply: Jul 29, 2013 8:44 AM by Friedrich Hofmann RSS

    Modify JOIN to get 0 (not a null value)

    Friedrich Hofmann

      Hi,

       

      I am building a diagram from personell data and I have an issue with a number of PNs that I "see" - I can have the exact nr. of emps displayed), but that are not in my diagram.

      The reason seems to be that I have three tables:

      - One for the entire holiday_budget at the start of the year

      - One for the nr. of days that person has already taken off

      - One for the nr. of days that person has planned to take off.

       

      In my diagram, I want to information about the nr. of days a person has neither taken nor planned yet: I have some categories as my dimension and as formula I want to show the nr. of employees within this category.

      The problem currently seems to be that some employees have not planned any days yet. They are seemingly not represented in the third table at all.

      <=> At one point in my script, I have a table where I have every PN, that employee's holiday_budget and a nr. of days that employee has taken. I join that to the third table to get the nr. of days that employee has planned into the same table - BUT the JOIN assigns null values (no value at all) to rows that are not present in that table - that means that PNs that have no days planned and are thus not present in the corresp. table have no value.

       

      <=> I then calculate like >> (hol_budget - days_taken - days_planned) = hol_leftover <<

      => For those that have no days planned, I get no result here, thus I cannot display them in my diagram.

       

      I have to find a way now to fill those empty cells in my resulting table with 0s.

       

      Can anyone help me there?

      Can I apply an ISNULL function to find those records where I have no value in that column?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler