topic Re: Modify JOIN to get 0 (not a null value) in QlikView

Modify JOIN to get 0 (not a null value)

datanibbler — Mon, 29 Jul 2013 10:51:58 GMT

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

Re: Modify JOIN to get 0 (not a null value)

Miguel_Angel_Baeyens — Mon, 29 Jul 2013 10:54:12 GMT

Hi,

Probably this document by Henric Cronstrom

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

is the best reference you can find to do this in QlikView and not in the source database.

Hope that helps.

Miguel

Re: Modify JOIN to get 0 (not a null value)

datanibbler — Mon, 29 Jul 2013 12:24:06 GMT

Hi miguel,

no, unfortunately that doesn't help me yet - the problem is, for some PNs (personell_numbers), I don't have ANY data in the table for planned_holidays. There is nothing I could use to fill up the missing values.

The problem clearly arises at the point when I join that table to the prior one: I am using a LEFT JOIN, so I should get a result table with all PNs in it, but simply no value in the column "planned" for some of them.

=> In principle, I should be able to use an ISNULL function to find those out.

I'll do some more testing.

Do you have any more ideas for me?

Thanks a lot!

Best regards,

DataNibbler

Re: Modify JOIN to get 0 (not a null value)

datanibbler — Mon, 29 Jul 2013 12:44:54 GMT

Hi Miguel,

I can't help myself, QlikView is strange sometimes - don't ask me what I've actually done differently now - nothing as far as I can tell though I have moved the ISNULL function here and there - but it seems to be working now.

Let me see...