topic Re: line break in source field in QlikView

line break in source field

spikenaylor1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: line break in source field

ecolomer — Sun, 19 Mar 2017 12:13:30 GMT

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,

Re: line break in source field

spikenaylor1 — Sun, 19 Mar 2017 12:24:19 GMT

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

Re: line break in source field

ecolomer — Sun, 19 Mar 2017 12:30:27 GMT

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

Re: line break in source field

Anil_Babu_Samineni — Sun, 19 Mar 2017 12:31:30 GMT

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

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

Re: line break in source field

rubenmarin — Sun, 19 Mar 2017 12:39:01 GMT

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), '')

Re: line break in source field

Anil_Babu_Samineni — Sun, 19 Mar 2017 13:02:47 GMT

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

Re: line break in source field

MarcoWedel — Mon, 20 Mar 2017 00:16:58 GMT

another optionmight be:

instead of

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

use

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

hope this helps

regards

Marco

Re: line break in source field

lawrenceiow — Fri, 05 Jan 2018 16:40:32 GMT

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