Full Outer Join Results and Downstream Problems

[Former Member]

Using xMII 11.5 SP3, I'm using the Joiner action with a full outer join to pull monthly consumption data out of an SAP custom table. I'm joining the tables based on the month field. Most times there is a match in both tables and life is good. Occasionally a month is missing in either table and I get something like this:

Cons1Date Cons1Qty Cons2Date Cons2Qty

03/01/2006 0:00 110513400 TimeUnavailable NA

In a subsequent 'Calculated Columns' block, I want to create a new column that's the sum of the two consumptions. In the case of a missing month (no consumption that month), I need the NA to be treated as a zero. In the calculated column action configuration I tried this:

Total Consumption = if(Cons1Qty='NA' , Cons2Qty, if(Cons2Qty='NA', Cons1Qty,Cons1Qty+Cons2Qty ))

but it returns zeros for all rows. A syntax issue? A logic issue? Or just plain a dumb approach to begin with?

Any ideas?

David

[jcgood25 · Accepted solution]

David,

Any chance to nip this problem in the beginning instead of manipulating it downstream?

Assuming you are building an IllumDoc object based upon the results of your SAP consumption data request in order to feed into the Joiner action, wouldn't it make sense just to have the zero values put into the document in the first place, then your Calc Column would just simply do the math?

Regards,

Jeremy

[Former Member · Accepted solution]

Som - thanks for the syntax help. I'm sure that'll do the trick.

Jeremy - one way I could do it upstream is to run down the IllumDocs for both consumption queries, find any missing months and add rows for those months using zero for the quantities, then do the join after which the calculated column action would be OK.

I just stumbled on another option. There are handy parameters in the Join block (NullDateValue, NullStringValue and NullNumberValue). I set the NullNumberValue parameter to zero - the missing month row now has a zero in the consumption field instead of an NA and that took care of the calculations.

Thanks for your help on this guys!

[Former Member]

David

Always use double (==) in IF()

i.e <b>Total Consumption = if(Cons1Qty=="NA" , Cons2Qty, if(Cons2Qty=="NA", Cons1Qty,Cons1Qty+Cons2Qty ))</b>

Hope u know the logical syntax expression for If() function, it always use boolean type data using <b>==</b>

Regards

Som

[jamie_cawley]

Try using the function

number(Cons2Qty)

Regards,

Jamie