Seperate Data within Column

[cognosbuddy]

Hi Friends,

So, I have a column data (let us name the column Forecast) that has forecasts (let us say Forecast1, Forecast2). Each has revenue associated with it.
Now, I would like to split this column into two separate columns- Forecast1 and Forecast2.
How can I do it? Could you please help me?

Thank you

[cognostechie]

Not sure what you mean - If you already have revenue associated with Forecast1 and 2 then you already have 2 columns so what did you want?

[BigChris]

You could make a crosstab with the Forecast field as the column headings...

[Michael75]

I would like to split this column into two separate columns- Forecast1 and Forecast2.

On what basis would you make this split? Do you have a flag or an indicator which tells you which type of forecast is contained in a given record? If you do, you could define two columns Forecast1 & Forecast2 as follows:

Code Select Expand

/* Forecast1 definition
if (Ind = 'F1') then (Forecast) else (0)

/* Forecast2 definition
if (Ind = 'F2') then (Forecast) else (0)

[cognosbuddy]

Guys,

Thank you for your replies. I think I did not do a great job in explaining the problem at hand.

I have created an excel file explaining the problem.

Cognostechie, I think you might have a better idea of what I need when you see attached file.

BigChris, I have done that. I am proceeding to more complexity after creating the crosstab. I think you will have a better idea when you see the attached file.

Michael, I cannot follow your logic because the column names change every month. You will get a better idea I think when you see the excel file.

Could you please help guys,

Thank you.

[cognostechie]

The names 'Current Forecast' and 'Previous Forecast' are relative so it depends on what that means. Is your business redefining Forecasts based on the how the actual sales go from month to month ? If they are providing snapshots then you can capture those snapshots in a file to create a history out of it and timestamp that Forecast. That way, you can resolve the changing name problem and you will be able to report on any Forecast or any number of Forecast in the same report. This should be done in the ETL first and once you have this table then you can put the Forecasts and the variances in the report.