topic Re: Re: Loading and Splitting Data from Excel in QlikView

Loading and Splitting Data from Excel

hopkinsc — Mon, 16 Sep 2013 16:26:47 GMT

Hi,

i was wondering if it was possible to load in the attached data (Excel) and split the combined columns into seperate fields?

Company Name and Address	Contact
company A	Tel: 123456789
Company House	Fax: 123456789
Company street	email: Company@Company.com
Company City	www.company.com
Compnay Country
Post Code

So i would want the Company Name and Address split into their own fields

Company Name

Address Line 1

Address Line 2

Address Line 3 etc

and then the same with the contact column.

Any suggestions will be appreciated.

Thanks

Re: Loading and Splitting Data from Excel

hopkinsc — Tue, 17 Sep 2013 07:47:27 GMT

Does anyone have any suggestions please?

Re: Loading and Splitting Data from Excel

Andrea_Ghirardello — Tue, 17 Sep 2013 07:48:06 GMT

You need to add a load from file statement, enable transformation steps and use the transpose feature in the Rotate tab.

See attached sample.

Re: Loading and Splitting Data from Excel

hopkinsc — Tue, 17 Sep 2013 08:08:40 GMT

Hi Andrea,

Thanks for your reply although the example is not correct.

In the example you have attached, you have made the field names everything in column A and the values are then everything in Column B

What i actually need is..

Cell A1 (Company Name and Address) - I don't need this (this is the field name, but i need the values below it broken down against different field names)

Cell A2 (Company A) - I want a new field created called 'Company Name' with the value in Cell A2 to be the actual value of the new field.

Cell A3 (Company House) - I want a new field created called 'Address Line 1' with the value of Cell A3 to be the actual value of the new field

and the same with the rest of column A

Column B again is the same..

Cell B1 (Contact) - I don't need this (this is the field name, but i need the values below it broken down against different field names)

Cell B2 (Tel:123456789) - I want a new field created called 'Telephone No' with the value in Cell B2 to be the actual value of the new field.

Cell B3 (Fax:123456789) - I want a new field created called 'FAX No' with the value in Cell B3 to be the actual value of the new field.

etc..

Can anyone help please?

Re: Loading and Splitting Data from Excel

Tue, 17 Sep 2013 08:11:55 GMT

Hi,

Can you let me know if there is any fixed seperator used to seprate the details. Like , or : or / e..t.c

Thnaks,

D J

Re: Loading and Splitting Data from Excel

hopkinsc — Tue, 17 Sep 2013 08:20:54 GMT

Hi DJ,

There is no seperator in Column A. Column B will have a colon ':' to seperate the values from the descriptions.

Tel:123456789

I suppose the TEL part could be the field name if that's easier but if not i would want to completely ignore the TEL.

Is that possible?

Re: Loading and Splitting Data from Excel

tresB — Tue, 17 Sep 2013 08:34:58 GMT

If you want to keep 123456789 and remove Tel:, try like:

Load

KeepChar(YourfieldwhichcontainTel:123456789likedata , '0123456789') as TelPhNo

Re: Loading and Splitting Data from Excel

hopkinsc — Tue, 17 Sep 2013 08:44:56 GMT

Hi Tresesco,

Thanks, i know how to do that, but its the whole load script i don't know how to do.

Thanks

Re: Re: Loading and Splitting Data from Excel

tresB — Tue, 17 Sep 2013 09:22:19 GMT

Hi,

I guess that, this is not what you want(believe your real life issue is something different), but hoping this would give some idea towards the solution. PFA.

Re: Re: Loading and Splitting Data from Excel

Andrea_Ghirardello — Tue, 17 Sep 2013 09:29:52 GMT

Ok...now it's more clear.

Try this

Re: Loading and Splitting Data from Excel

hopkinsc — Tue, 17 Sep 2013 09:38:46 GMT

Thanks, thats exactly what i was trying to do! thanks for your help!