line break in source field

[spikenaylor1]

My excel files have a field containing a line break.

example.

Field Name:     BatchStrain

Text in Field:    This is Line 1

                       and This is Line 2

Qlickview loads the field correctly and shows it in a table or list as

This is Line 1

but when you hover over it, shows

This is Line 1

and this is Line 2

I need to add a value to this on load:

If(BatchStrain='This is Line 1', 'Value') as NewValue

This will not assign a value as it cannot see the line break and the second line.

I cannot work out how to include the line break in the formula to assign a value.

Anyone got any pointers.

Regards

[ecolomer]

But you need all the text (This is Line 1  and this is Line 2) in an field or in two fields?

If you want all the text in the same field, you should do nothing.

If you want it in two different fields, use the function "Subfield", but before you should see that carcater is the one that causes it to be divided into two lines.

Saludos,

EC

[spikenaylor1]

I need to assign a value on load to that field.

example

In Excel the Column name is BatchStrain

within this column some of the data is like:

Value 1 Text

value 2 Text

Value 3 (LineBreak) text    (The real data is longer and is used in other software and a line break is required for long text)

In the qlikview load script

I need to assign a value depending on what value I get

my load script line is:

If(BatchStrain='Value 1 Text','Number1',If(BatchStrain='Value 2 Text',"Number2',If(BatchStrain='Value 3 Text','Number3'))) as NumberText

It will not assign anything to the 3rd option as there is a line break in there confusing matters.

Regards

[ecolomer]

Try with this:

If(BatchStrain='Value 1 Text','Number1',If(BatchStrain='Value 2 Text',"Number2',If(left(BatchStrain, 12)='Value 3 Text','Number3'))) as NumberText

[Anil_Babu_Samineni]

Still doubt, What are you trying to do over here. May be try a luck

If(SubStringCount(BatchStrain)='Value 1 Text','Number1',If(SubStringCount(BatchStrain)='Value 2 Text',"Number2',If(SubStringCount(BatchStrain)='Value 3 Text','Number3'))) as NumberText

Or

If(BatchStrain='Value 1 Text','Number1',If(BatchStrain='Value 2 Text','Number2',If(left(BatchStrain, 12)='Value 3 Text','Number3'))) as NumberText

[rubenmarin]

Hi Michael, that will not assign the value because the strings are different, if you want to assign a value if the string starts with 'This is Line 1' you can use:

If(Wildmatch(BatchStrain, 'This is Line 1*'), 'Value') as NewValue //This is not case sensitive

or:

If(Left(BatchStrain, 14)='This is Line 1', 'Value') as NewValue //This is case sensitive

You can also remove the line break using:

Replace(BatchStrain, chr(10), '')

[Anil_Babu_Samineni]

Testing done

LOAD BatchStrain as BatchStrainCopy,If(BatchStrain='Value 1 text','Number1',If(BatchStrain='Value 2 text','Number2', If(BatchStrain,SubStringCount(BatchStrain,'Value 3 text'),'Number3'))) as NumberText ;

LOAD * Inline [

BatchStrain

Value 1 text

Value 2 text

Value 3 (LineBreak) text

];

Images for you

[MarcoWedel]

another optionmight be:

instead of

If(BatchStrain='Value 3 Text','Number3')

use

If(BatchStrain like 'Value 3 Text*','Number3')

hope this helps

regards

Marco

[lawrenceiow]

Mike,

Try a method like this...

LET vString = 'This is line 1' & Chr(10) & 'and this is line 2'

If(BatchStrain='$(vString)', 'Value') as NewValue