Cross Tab and Transpose

[sagarb]

Hello,

I have this simple dataset as shown below

Month	Incoming Calls	Avg Incoming Call Time	Outgoing calls	Avg. Outgoing Call Time
Jan	5	10	30	15
Feb	6	12	40	5

I am trying to get it into this format

Month	Call Type	No of Calls	Avg Call Time
Jan	Incoming	5	10
Jan	Outgoing	30	15
Feb	Incoming	6	12
Feb	Outgoing	40	5

I thought it was going to be a simple calculation but I am not able to get it into the desirable format. I am using Transpose but I guess I am not using it the right way. Thanks for your help.

[tkhan]

Hi ben, so the null values, are because of the cross-tab function, and I cannot figure out a way to clean the data .

Any other tricks your sleve? I have attached a few screenshots for you to take a look

[tkhan]

Yea the nulls are a result of the cross tab, and the data cleanse tool doesnt see the columns, any other suggestions?

[BenMoss]

You could use the ‘multi field formula tool’ which can be set to include ‘unknown fields’ as selected, and write a formula along the lines of...

If isnull([_currentfield]) then ‘this’ else [_currentfield_] endif

Hope this helps.

Ben

[tkhan]

Worked like a charm Ben,

Thanks for the help