topic Re: String limitation in App Development

String limitation

maxsheva — Mon, 11 Feb 2019 16:18:46 GMT

Hi,

I have found a limitation in Qlik Sense (didn't check in QlikView) in a string of a Field name.

Please see "QV script - Fails" as attachment. It fails when try to load "Field149"

If remove "Field149" from script rows 3,9 it will work properly (see file "QV script - OK")

I need to generate string for further insert into SQL. Currently I didn't find another way how to create the same result.

Any help will be much appreciated!

Re: String limitation

maxsheva — Mon, 11 Feb 2019 17:04:40 GMT

Hi @sunny_talwar

Could you please help me with this issue?

Re: String limitation

pradosh_thakur — Tue, 12 Feb 2019 05:24:28 GMT

I dont think this is the field149 creating the issues. If you remove a few characters in Concat function the script will work. This might be because of any limit on the script function character. I would love to know more on this.

I hope @rwunderlich will be able to guide us on this.

Thanks

Pradosh

Re: String limitation

marksouzacosta — Tue, 12 Feb 2019 05:51:46 GMT

This problem reminds me some of the limitations that we have in the ODAG apps when we try to pass too many values to a variable using LET. I don't know this limit but maybe this ODAG page can help you to find a solution:

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/LoadData/bind-expressions-OnDemand-template-app.htm

By the way, at the end what are you trying to achieve?

Re: String limitation

marcus_sommer — Tue, 12 Feb 2019 08:26:40 GMT

Currently I don't have Sense available and couldn't therefore check for any possible limitations. In View in worked without any problem.

But I think you could change the variable-content to a real string instead of an expression which is executed from the $-sign expansion. This means the variable-content might be look like:

Field1,Field2,Field3, ...

instead of

chr(39)&"Field1"&chr(39)&','&chr(39)&"Field2"&chr(39)&','&chr(39)&"Field3"&chr(39)&',' ...

This will simplify the approach as well as reducing the length of the string and might therefore uplift the treshold for the number of chars. You could create this structure as well as with a concat-aggregation or maybe also with an interrecord-function within a preceeding load like:

...
Temp1:
load peek('Values') & Field as Values;
load 'Field' & recno() & if(recno() < 149, chr(44)) as Field autogenerate 149;

LET vValues = Peek('Values',-1,'Temp_1');

Str: noconcatenate Load $(vValues) Resident Data;
drop tables Data;

- Marcus

Re: String limitation

maxsheva — Tue, 12 Feb 2019 08:51:42 GMT

Hi @pradosh_thakur,

Most likely it is chr() limitation. I loaded successfully 10 000 simply combined '1' values e.g. '1,'&'1,'...
But string with chr(39) fails on approximately 5000 length.

Re: String limitation

marcus_sommer — Tue, 12 Feb 2019 08:59:52 GMT

This might be the cause and I'm not surprised because there are various similar limitations to the number of (nested) functions. For example within the View release 11 there is a restriction of 99 if-loops within a single expression and also in the current releases of View and Sense are such limitations (maybe the number of them may have changed) intentionally enabled.

- Marcus

Re: String limitation

maxsheva — Tue, 12 Feb 2019 10:49:54 GMT

Hi @marksouzacosta,

Thanks for provided url but it generates different result.

It combines all values from one field with a defined chr(). In other words it works with columns.

e.g.

LOAD * INLINE

[ Field1

BOS

JFK

ORD ];

Result 'BOS','JFK’,'JFK'

For my use case values should be combined from all fields per each row into one string delimited by quotes and comma.

So it should work with rows.

LOAD * INLINE

[ Field1, Field2, Field3

BOS1, BOS2, BOS3

JFK1, JFK2, JFK3

ORD1, ORD2, ORD3

];

Expected result

row1: 'BOS'1,'JFK’1,'JFK1'

row2: 'BOS'2,'JFK’2,'JFK2'

row1: 'BOS'3,'JFK’3,'JFK3'

As I mentioned in my first post it should be passed into SQL INSERT statement as VALUES().

INSERT INTO [dbo].[Table1]

([Field1], [Field2], [Field3])

VALUES

('BOS'1,'JFK’1,'JFK1'), ('BOS'2,'JFK’2,'JFK2'), ('BOS'3,'JFK’3,'JFK3')

I have already found a solution where split string into two parts. Please see attachment ("QS script updated" and "QS script result").

Thanks

Re: String limitation

maxsheva — Tue, 12 Feb 2019 11:08:14 GMT

Hi @marcus_sommer,

Thanks for suggestions. In my previous post I found a solution but not sure it is the best one.
At least works fine for this particular case.

I suppose this is limitation in latest releases but I would like to know where exactly it started to appear.

Could you please advise who is right person I may ask for?

Thanks.

Re: String limitation

marcus_sommer — Tue, 12 Feb 2019 12:19:36 GMT

AFAIK there is no public documentation about such limitations. Maybe Henric Cronström could shed some light to it.

Running in such limitations is a strong indicator that the used logic has more or less weaknesses and a rethink of the whole task is often better as looking for any ways to bypass it. This means knowing the exact theshold is rather not very useful.

Like in my other answers suggested approach is the creation of real strings probably better and your alternatives to the variable expression-content. Also there might be other (loop) approaches easier to create such strings.

Beside this I could imagine that creating a csv-output of these data which is then imported through your database might be much more convenient as creating load/sql-statements on the fly.

- Marcus

Re: String limitation

maxsheva — Tue, 12 Feb 2019 13:37:03 GMT

Thanks Marcus,

I have tried to understand how to implement your suggested solution but still not quite clear.

I need to get a result with a combined and delimited values from existing table.

If I use in a variable a content such as Field1,Field2,Field3 it will not give me 'Field1', 'Field2', 'Field3' as result.

csv-output is a good idea. I will think about it.

Thank you!

Re: String limitation

marcus_sommer — Tue, 12 Feb 2019 13:55:05 GMT

My suggestion was related to your first load-statement which looked like creating a field-list for loading data within a load-statement and contained therefore no wrapping single-quotes. But you could just add them. Also the more essential part of my suggestion was to create a complete string within the variable and not an expression.

- Marcus

Re: String limitation

marksouzacosta — Tue, 12 Feb 2019 14:01:17 GMT

Do you need to pass all the values at the same time in one single INSERT statement?

If you don't you can call an insert command for each one of the data rows that you have, so your code will be something like:

[DataSource]:
LOAD ........(something);

For vIndex = 0 To NoOfRows('DataSource') - 1

//Build your query inside here

LET vField1 = Peek('Field1',vIndex,'DataSource');
LET vField2 = Peek('Field2',vIndex,'DataSource');
LET vFieldn = Peek('Fieldn',vIndex,'DataSource');

SQL INSERT INTO YourTable
(
[Field1],
[Field2],
[Fieldn]
)
VALUES
(
'$(vField1)',
'$(vField2)',
'$(vFieldn)'
)
;

Next vIndex