Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Acct Div (Dist) | Acct Reg (Off) | Acct (Cust) | Fisc Yr | Fisc Yr / Period | Rev Type Grpg | Rev Type | Amount | |||||
909 | Ontario West | 900 | Ottawa | 9009 | SUDBURY | 2012 | Period 01 2012 | Decl Val | Rev Type Amt | $ | 420 | |
909 | Ontario West | 900 | Ottawa | 9009 | SUDBURY | 2012 | Period 01 2012 | Decl Val | Rev Type Shpmts | 1 | ||
909 | Ontario West | 900 | Ottawa | PR01 | SUDBURY | 2012 | Period 01 2012 | Sat-Del | Rev Type Amt | $ | 23.00 | |
909 | Ontario West | 900 | Ottawa | /PR01 | SUDBURY | 2012 | Period 01 2012 | Sat-Del | Rev Type Shpmts | 2 |
Hello all,
I am trying to have two column for Rev types (Rev Type Amt [which is a $ value] and Rev Type Shpmts [which is a Quantity]) instead of one Rev Type column. I am trying to have them separate as one is a $ value which I want to add and show subtotal where as other is just a number (quantity). I could acive the separation using a pivot table dragging rows to column feature but the problem there is that those columns are not trated individually, as a result my expression sum(Amount) is still adding $ amt + no. of shipments. I cannot hide that column as I created a pivot table.
What would be a workaround? Do I need to add a calculated expression? If so how? Is there a way I can achieve this row to column conversion in load script itself ?
you can put an if condition the load script itself to pull the rev type column as 2 separate columnss;
eg. load field1,
..
if(revtype>100, revtype) as [revtype amount],
if(revtype<=100, revtypes) as [revtype shpmts],
fieldn
..
You can specify the condition inside if depending on how to differentiate currency value from number values.
Thank you Roma. That was helpful. As my F12 column in excel indicates $ value with $ sign and everything else as Null I used,
if(F12='$', [Amount]) as [revtype amount],
if(isnull(F12), [Amount]) as [revtype shpmts]
Now this seems to get me the values as wanted but then my pivot table looks like this:
How can I get rid of the 0 or -. Basically the null values. Ideally I would want the revtype amount beside the corresponding revtype shpmt.
In that case, you can use 2 separate loads, with other common attribute as a point of join;
eg. If you have id as common attribute;
load Id,
[Amount] as [revtype amount],
amount_field_1,
amount_field_2,
...
from tablename
where F12='$'
join
load Id,
[Amount] as [revtype shpmnts],
shpmnts_field_1,
shpmnts_field_2,
...
from tablename
where isnull(F12);
Hope this helps..