the FM functions Trim and Replace can come in handy cleaning up your imported data.
Are you importing the data into FM, or are you in FM and using File>Open to open the .xlsx file?
I opened your file using File>Open and FM brought both columns in as text fields. So, you might need to use some conversion functions, like GetAsTime and/or Time, along with text manipulation functions.
As another route, can you get a .CSV file instead?
There are many ways to go about forcing this to work.
don’t know where your comas ( “ , ” ), or colons ( “ : ” ) are coming from. I have simply dragged the xlsx file unto the OS X, FM 15 Adv icon, set the first row to labels, and the file opens as per the figure below.
If there is a space only in column “Problem A”, then you can create a new field (Fix Problem A) with the following calculation:
= If ( Left ( Problem A ; 1 ) = “ “ ; “” ; Problem A )
As for values in column Problem B, I think you are suggestion that 10.1 is actually 10:10:00.
Are you sure it is not 10:06:00 (10 hrs, 6 min, 0 sec). Where 06 min = .1 hrs)
Need more info to properly interpret you data.
Hi David, hi rouelf,
Thank you very much for pointing me in the right direction! I've found a solution to both problems. I'm not sure whether they are the most elegant approaches, however, they work:
a) I've ended up using
TrimAll ( Table::Problem A ; 0 ; 0 )
It seems to do the job perfectly without causing any unwanted results.
b) Rouelf, yes, I'm sure that the imported 1.1 is supposed to be 1:10:00. Putting it together I've came up with:
GetAsTime ( Substitute ( If ( Length ( Table::Problem B ) - Position ( Table::Problem B ; "," ; 1 ; 1 ) < 2 ; Table::Problem B & "0" ; Table::Problem B ) ; ["."; ":"];[","; ":"] ) )
Thanks a lot!
Thanks to you too - especially for the comfortable solution file!
a) Your approach for problem A works fine:
Case ( Length ( Self ) = 1 and Self = " "; ""; Self )
b) Regarding problem B I extened it from
Substitute ( Self ; "." ; ":" ) & ":00" to Substitute ( Self ; ["."; ":"]; [","; ":"] ) & ":00"
since I forgot to mention that I'm working on an International system and thus I needed to take care of the decimal coma vs. point problem. Nevertheless I've ended up with wrong values for 10.1, 10.2, etc. being transformed to 10:01:00 resp. 10:02:00 instead of 10:10:00 or 10:20:00.
Anyway, your help was very much appreciated!