Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a problem. in my table, field name and date field are in single column. how to separate the date feild names and make a three columns like date, budget and actual. i have 2 another field's before this namely Department and Region. App Development
2014 Jan Actual | 2015 Jan Budget | 2015 Jan Actual | 2014 Feb Actual | 2015 Feb Budget | 2015 Feb Actual | 2014 Mar Actual | 2015 Mar Budget | 2015 Mar Actual | 2014 Apr Actual | 2015 Apr Budget | 2015 Apr Actual |
---|---|---|---|---|---|---|---|---|---|---|---|
14,72,558 | 16,63,991 | 16,81,062 | 13,71,603 | 15,49,911 | 17,41,413 | 12,33,446 | 13,93,794 | 18,86,986 | 13,98,630 | 15,80,452 | 16,70,204 |
14,41,072 | 16,28,411 | 18,30,590 | 14,65,148 | 16,55,617 | 18,24,825 | 16,59,884 | 18,75,669 | 21,43,521 | 16,85,532 | 19,04,651 | 19,49,451 |
After the CROSSTABLE LOAD, your column headers will reside as values in a newly created field. Let's say that you called it 'Item' and the Amount has been stored in a field called 'Amount'..
Reload the resulting table in a new resident table by adding these two column definitions:
NewTable:
LOAD Department, Region, Item, Value,
Date#(Left(Item, 8), 'YYYY MMM') AS DateMY,
Mid(Item, 10) AS Type
RESIDENT OriginalCrossedTable;
:
Best,
Peter
try the crosstable function
by using crosstable i can join all the column but, my question is how to seperate the fields , actual,budget and date.
crosstable does not join columns. A typical example could be to have one column per month. To transform a cross table into a straight table, use a crosstable prefix.
A cross table is a common type of table featuring a matrix of values between two or more orthogonal lists of header data, of which one is used as column headers. A typical example could be to have one column per month. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. month names) will be stored in one field - the attribute field - and the column data (month numbers) will be stored in a second field: the data field.
The syntax is:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
where:
attribute field name is the field to contain the attribute values.
data field name is the field to contain the data values.
n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.
Crosstable (Month, Sales) Load * from ex1.csv;
Crosstable (Month,Sales,2) Load * from ex2.csv;
Crosstable (A,B) Select * from table3;
try with subfiled()
SubField(SubField(`Authoriser comments`,'</p>',1),'<p>',2) as `Authoriser comments`, ----this fumctuion will helps lot to fetch part of field data.
After the CROSSTABLE LOAD, your column headers will reside as values in a newly created field. Let's say that you called it 'Item' and the Amount has been stored in a field called 'Amount'..
Reload the resulting table in a new resident table by adding these two column definitions:
NewTable:
LOAD Department, Region, Item, Value,
Date#(Left(Item, 8), 'YYYY MMM') AS DateMY,
Mid(Item, 10) AS Type
RESIDENT OriginalCrossedTable;
:
Best,
Peter
thanks a lot man. it worked.