topic adding to null, a value from another table in QlikView

adding to null, a value from another table

Fri, 14 Oct 2011 08:21:47 GMT

hi everyone,

i have a table that contains values by month and category
and another table which contains month and value

it's not obligatory to have data for a certain category in all months

i'd like to display in a chart all the months and for a certain category, the sum of the values from the 1st and 2nd table.
this works fine when this category has a record by month in the 1st table, so if in the 2nd table there is a value for that month, it adds it up.
but in case the category doesn't have a record in the first table for a specific month, it won't take into consideration the value for that month from the 2nd table.

can someone find a solution for this without having to change in the script by adding a corresponding record in the 1st table for the missing category/month ?

thx for ur help.

i've attached a sample qvw to clarify my question: for example i want to display for category 'B' the value '10' in january (null from the 1st table and 10 from the 2nd).

adding to null, a value from another table

ali_hijazi — Fri, 14 Oct 2011 13:05:20 GMT

add the following script to your original script

tempCategory:

LOAD Distinct Category as tempCategory

Resident MonthCategory;

join(tempCategory)

LOAD Month as month Resident Months;

inner join(tempCategory)

LOAD Category as tempCategory

Resident MonthCategory;

inner join(MonthValue)

LOAD tempCategory, month as Month Resident tempCategory;

drop Table tempCategory;

Concatenate(MonthCategory)

LOAD

Month,

Value2 as Value,

tempCategory as Category

Resident MonthValue;

drop Table MonthValue;

finally make your expression as sum(Value) only

adding to null, a value from another table

Mon, 17 Oct 2011 17:18:55 GMT

hey Ali,

thanks for your answer. there's no doubt that it was a lot helpful as i got your point then changed a bit in the script to get the desired result.

here's the final added script:

tempCategory:

LOAD Distinct Category as tempCategory
Resident MonthCategory;

Join

LOAD Month as month
Resident MonthValue;

Join

LOAD Month as month,
Category as tempCategory,
Value
Resident MonthCategory;

DROP Table MonthCategory;

MonthCategory:
LOAD month as Month,
tempCategory as Category,
Value
Resident tempCategory;

DROP Table tempCategory;

As for the expression:

if(Category = 'B',
sum(Value) + sum(Value2)
,
sum(Value)
)