Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

Modify JOIN to get 0 (not a null value)

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

3 Replies

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

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

datanibbler
Esteemed Contributor

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

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

datanibbler
Esteemed Contributor

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

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...

Community Browser