Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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), '')
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
another optionmight be:
instead of
If(BatchStrain='Value 3 Text','Number3')
use
If(BatchStrain like 'Value 3 Text*','Number3')
hope this helps
regards
Marco
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