Row to column transformation as there is a difference in the data type

[#1]

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 ?

[#2]

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.

[#3]

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.

[#4]

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..