Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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?
Yes, but I not only want them to be renamed but also group them so that they become values under the new Field Month.
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..
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.
May be check this link for cross table help.
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
Can you post the sample data / table structure view?
This is currently my table structure.
Hi,
Check the Attachment. I put comments in the Script.