Take individual fields and group them under one field.

[neena123]

Hello,

I have 12 individual fields I am pulling in from Oracle. They are label Sales1, Sales2, Sales3,....etc. Each field have the sales value of a month. So Sales1 is Jan. and Sales2 is Feb and so on....I want to change the name of each field to the correct month and then I want each of the months to be grouped under 1 field so it would look like this:

Month                          This is how it looks now

Jan                                          Sales1

Feb                                          Sales2

Mar                                          Sales3      

Apr                                          Sales4

May                                         Sales5      

Jun                                          Sales6      

Jul                                           Sales7               

Aug                                         Sales8     

Sep                                         Sales9    

Oct                                         Sales10           

Nov                                        Sales11            

Dec                                        Sales12                     

I am trying to write an expression so that the Sales of the Previous month is always displaying and I do not want to manually change the expression each time. So if there is a way to group the individual fields and rename them to a Month Field I could then write a simple expression. I am hoping there is a way to achieve this! Any help is very much appreciated!!

[settu_periasamy]

May be you can create inline table, then try to use Rename fields.  Like

FieldMap:

MAPPING LOAD * Inline [

OldName,  NewName

Sales1,Jan

Sales2,Feb

......

.....

];

Rename Fields Using FieldMap;

Is this you are asking?

[neena123]

Yes, but I not only want them to be renamed but also group them so that they become values under the new Field Month.

[settu_periasamy]

So your sales data in separate coloumn. I think you need to look on cross table or take the individual field (i mean month) and concatenate that..

[hemhund2016]

With my understanding of requierment , this is what need to be done.

Your load script should be as below.

temptable:

LOAD * INLINE

[OraField

Sales1

Sales2

Sales3      

Sales4

Sales5      

Sales6      

Sales7               

Sales8     

Sales9    

Sales10           

Sales11            

Sales12    ];

for i=0 to fieldvaluecount('OraField')

vFieldname=Peek('OraField',$(i)) ;

vMonth=month(Replace(Peek('OraField',$(i)) ,'Sales'));

Maintable:

Load

$(vFieldname) as Sales,

$(vMonth) as Month

from

YOUR_ORACLE_SOURCE;

next i

This should group all the sales fields into single field.

Next step in the chart , you can use the previous function to show the previous month sales

Let me know if it helps.

[settu_periasamy]

May be check this link for cross table help.

Loading Cross Tables

[neena123]

Thanks For the reply but each Sales1 is its own field not a value of a Oracle field. I want group all the individual 12 Sales fields rename them to its corresponding month and then put those fields under 1 field labeled Month

[settu_periasamy]

Can you post the sample data / table structure view?

[neena123]

This is currently my table structure.

[settu_periasamy · Accepted solution]

Hi,

Check the Attachment. I put comments in the Script.