topic Re: Read Text when exporting from excel in QlikView

Read Text when exporting from excel

anuradhaa — Tue, 12 Jan 2016 16:12:14 GMT

ID	Lable
1	dfdfd,Sprint1,dgd
2	Sprint1
3	fdfdg,gdgfg,ghfhfh,Sprint1
4	fsfsdf,Sprint2
5	Sprint2
6	dfd,3434,gdg434,Sprint1,egrrg,555

I have above data set in excel. i want to find the the word 'Sprint%' in the column Lable and store the value (like Sprint1,Sprint2) in a separate column call Sprint when loading that excel. is it possible.

Re: Read Text when exporting from excel

Anonymous — Tue, 12 Jan 2016 16:23:57 GMT

use function mid:

=mid(Lable,FindOneOf(Lable,'Sprint%'),7) - works if Sprintx has only values 0--9

if your number after Sprint contain more than 1 Digit you Need to find the right comma after Sprint to get the correct lenght (instead 7)

Re: Read Text when exporting from excel

anuradhaa — Wed, 13 Jan 2016 17:22:51 GMT

here when there is a text startng with S then it is also consider as 'Sprint%' when we use finfoneof

Re: Read Text when exporting from excel

tobivogt — Thu, 14 Jan 2016 21:20:33 GMT

Hi,

maybe splitting the substrings in Lable field would make it a bit easier.

Meets the following script your requirements:

TEST:

LOAD * INLINE [

ID, Lable

1, "dfdfd,Sprint1,dgd"

2, "Sprint1"

3, "fdfdg,gdgfg,ghfhfh,Sprint1"

4, "fsfsdf,Sprint2"

5, "Sprint2"

6, "dfd,3434,gdg434,Sprint1,egrrg,555"

];

NEU:

LOAD * where SUB_LABLES like 'Sprint*';

LOAD

subfield(Lable,',') as SUB_LABLES

resident TEST;

Regards,

Tobias