Change missing values as "0"

[#1]

Hi Guys,

I'm with the following problem: 2 tables are linked and if some register is missing in one of the tables no value is returned. But i need to obtain "0" as value in this case.

For example:

TableA:

LOAD

           DATA,

           TIPO,

           Sum(DURACAO) as Total_Sum

Resident

          Table_BASE1

Group by

          DATA, TIPO;

TableB:

OUTER JOIN(TableA)

LOAD

          DATA,

          TIPO,

          Count(CHAMADAS) as Total_Count

Resident

          Table_BASE2

Group by

           DATA, TIPO;

The entire record is missing in "TableB". But i need to associate the value of the sum "Total_Sum" from "TableA" that the fact all records exists, with the values of the Count at "TableB" to calculate the correct average for the last 12 months.

The problem is that if the entire record is missing in "TableB", no data is returned and i need to obtain "0" as value in this situation.

Can anybody suggest a solution to this problem?

Thanks

[Anonymous]

Anderson

Instead of joining could you use the ApplyMap() function ?

Best Regards,     Bill

[#3]

Bill, i am trying to use this function, but i have a problem with my primary key and group by selections during the load.

Now, my question is: How can i populate all null values of the field with "0"? Any suggestion?

Thank you

[#4]

Hi,

You may use the alt() function that accepts two arguments. It returns the first arg if it not empty, it return the second argument if the first arg is empty.

You can also use sth like: if(isnull(....), ....)

Fabrice

[jonathandienst]

Hi

Assuming you want to do this in the load script, and apply map is not right for your requirement (I see a full outer join in your script), then you will need to do a second pass through the table:

Table_A1:

LOAD

           DATA,

           TIPO,

          If(Len(Total_Count) = 0, 0, Total_Count) As Total_Count,

          If(Len(Total_Sum) = 0, 0, Total_Sum) As Total_Sum

Resident

          Table_A

DROP Table_A;

Or, more efficiently:

     Alt(Total_Count, 0) As Total_Count

     Alt(Total_sum, 0) As Total_Sum

HTH

Jonathan

[#6]

Hi, thanks for the suggestions, but both methods is not working yet.

Here i have a success result, because when i select the date its possible to obtain the "Css_Total" and "Cyl_Total".

And here i have an error result, because when i select the date, i don't have any "Css_Total" associated. This is my problem, i need to obtain "0" as value in this case.

Can anybody suggest another solution for this problem?

Thanks

[#7]

Hi, thanks for the suggestions, but both methods is not working yet.

Here i have a success result, because when i select the date its possible to obtain the "Css_Total" and "Cyl_Total".

And here i have an error result, because when i select the date, i don't have any "Css_Total" associated. This is my problem, i need to obtain "0" as value in this case.

Can you suggest another solution for this problem?

Thanks

[#8]

Did you or anyone find a solution to this problem. I have the same problem in pivot table. I have to change the color of some dimensions in pivot table but there are missing values associated with those dimensions and I searched some solutions but no luck yet. And I could use any of script side or front end solution. I also want to replace the missing values with any dummy value so that I can avoid the color problem. If anyone has found a solution to this please share it.