Date and Field name in single Column.

[amithmurali]

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

[giakoum]

try the crosstable function

[amithmurali]

by using crosstable i can join all the column but, my question is how to seperate the fields , actual,budget and date.

[giakoum]

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.

Crosstable

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.

Examples:

Crosstable (Month, Sales) Load * from ex1.csv;

Crosstable (Month,Sales,2) Load * from ex2.csv;

Crosstable (A,B) Select * from table3;

[buzzy996]

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.

[Peter_Cammaert · Accepted solution]

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

[amithmurali]

thanks a lot man. it worked.