Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a field called 'month' and I would like to covert it into a twelve fields. The process would be exacty the inverse as the Crosstable load does.
Does it exists a command to do this or I have to look for the way ?
By the way , does it exists a funtion to convert '01', '02', '03',... into Jan, Feb, Mar,...?
Thanks
If you want to create twelve month fields there is no special function that I'm aware of so you could do the following:
If(Month='Jan',Month) as Jan
If(Month='Feb',Month) as Feb
If(Month='Mar',Month) as Mar
etc..
The funciton you need to covert a number of a month to a literal is below.
=Month(Date#('01','MM'))
If you want to create twelve month fields there is no special function that I'm aware of so you could do the following:
If(Month='Jan',Month) as Jan
If(Month='Feb',Month) as Feb
If(Month='Mar',Month) as Mar
etc..
The funciton you need to covert a number of a month to a literal is below.
=Month(Date#('01','MM'))
You can do this with a for loop, there´s no pre-built function to do that.
Tmp:
LOAD
MonthName(today() - RecNo()) as Month,
Rand() as value
AutoGenerate 300;
YourData:
LOAD null() as n AutoGenerate 0; // fake table to use "Concatenate" onwards
for i = 1 to 12
Let MonthName = SubField('$(MonthNames)',';',$(i)); // MonthNames is a pre-built variable
Concatenate(YourData)
LOAD
if(SubField(Month,' ',1) = '$(MonthName)',value) as $(MonthName)
resident Tmp;
next
drop Field n;
There is a built-in function that does this. Generic Load.
HIC