Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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
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 tempCategoryAs for the expression:
if(Category = 'B',
sum(Value) + sum(Value2)
,
sum(Value)
)