Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I am loading an excel spreadsheet and the data is not 100% how I would like it ,
there is a column called Reasons, which may contain
"No receipt","No Authorisation","No Paper","No Ticket"
Or a combination such as
"No receipt,No Paper,No Ticket" , "No Authorisation,No Ticket"
I wish to be able to load the column as normal so as Reasons and have both singular reason and combined as results.
But then another load where the data is split by "," so for the combinations have
"No receipt","No Paper","No Ticket" - it would be the data text to columns function in excel dont know if there is an equivlent in QV
Can anyone advise ?
Thanks
Anne
I believe the excel function will fill in each substring into a different column, right? If you consider the Excel lines as records and columns as fields, do you really want to create new fields?
I assume that you probably don't want to do that, but I assume that you want to create new records per substring in one field, so you get e.g. in a list box of that field a line per substring.
You could use subfield for that.
If you have loaded your table
INPUT:
LOAD
recno() as RecID,
Reason,
..
from ExcelTable.xls;
You could do something like:
LOAD RecID,
subfield(Reason, ',') as ReasonSubs
resident INPUT;
Hope this helps,
Stefan
I believe the excel function will fill in each substring into a different column, right? If you consider the Excel lines as records and columns as fields, do you really want to create new fields?
I assume that you probably don't want to do that, but I assume that you want to create new records per substring in one field, so you get e.g. in a list box of that field a line per substring.
You could use subfield for that.
If you have loaded your table
INPUT:
LOAD
recno() as RecID,
Reason,
..
from ExcelTable.xls;
You could do something like:
LOAD RecID,
subfield(Reason, ',') as ReasonSubs
resident INPUT;
Hope this helps,
Stefan
Thanks Swuehl,