topic Load from Excel pull from substring in QlikView

Load from Excel pull from substring

Wed, 18 Jan 2012 16:25:31 GMT

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

Load from Excel pull from substring

swuehl — Wed, 18 Jan 2012 17:50:59 GMT

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

Load from Excel pull from substring

Thu, 19 Jan 2012 10:39:37 GMT

Thanks Swuehl,