topic Re: Why can I only paste 4000 rows in LOAD INLINE? in QlikView

Why can I only paste 4000 rows in LOAD INLINE?

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am copying 400,000 rows from a MySQL result onto the clipboard. I am trying to get these 400,000 rows into QlikVIew by going into the Script Editor, Insert >> Load Statement >> Load Inline.

Inline Data Wizard pops up, I navigate to Edit >> Paste and this only pastes the first 4000 rows of what is on my clipboard.

When I try pasting the same data into Excel, it pastes all 400,000 just fine and dandy.

Please can someone explain why the Inline Data Wizard limits me to 4000 rows and how to get rid of the limit?

Thanks

Re: Why can I only paste 4000 rows in LOAD INLINE?

oknotsen — Tue, 11 Jul 2017 15:17:14 GMT

I am more curious why you are not just connecting QlikView to that MySQL database and save yourself all the hassle?

Re: Why can I only paste 4000 rows in LOAD INLINE?

juraj_misina — Tue, 11 Jul 2017 15:26:28 GMT

Optionally, if for some reason you cannot directly connect to MySQL, save those results to a text/csv file and load that file.

Re: Why can I only paste 4000 rows in LOAD INLINE?

jmvilaplanap — Tue, 11 Jul 2017 15:39:03 GMT

An Inline table is normally for some type of internal info for a Qlik app, if you need to use more than 20 rows I think is better save this table into csv os excel file. It will be easier for the maintenance.

Regards

Re: Why can I only paste 4000 rows in LOAD INLINE?

Tue, 11 Jul 2017 15:50:51 GMT

LOAD INLINE seemed like it was able to recognize the comma-separated values below and give me the structure I wanted.

MySQL spits out a column with comma-separated values. Say it looks like this in SQLYog:

SUBSCRIBER	VALUES_SEARCHED
abcd	12345,67,90,99
abcd	68
abcd	2345, 12345
efgh	68,80,88
efgh	2

What I want to do is:

1. Separate all the substrings of VALUES_SEARCHED

2. Place them one underneath the other

3. Remove duplicates

and get this:

SUBSCRIBER	VALUES_SEARCHED
abcd	12345
abcd	67
abcd	90
abcd	99
abcd	68
abcd	2345
efgh	68
efgh	80
efgh	88
efgh	2

I understand that this is very cumbersome to do in with MySQL. Therefore, I am trying to paste the data in QlikView as exemplified in the table above and let QlikView do all the work by using the following script:

TABLE:

REPLACE LOAD *, SUBFIELD(VALUES_SEARCHED,',') AS SEPARATE_VALUE;

LOAD * INLINE [

SUBSCRIBER, VALUES_SEARCHED

abcd, "12345,67,90,99"

abcd, "68"

abcd, "2345, 12345"

efgh, "68,80,88"

efgh, "2"

];

Re: Why can I only paste 4000 rows in LOAD INLINE?

Tue, 11 Jul 2017 16:05:43 GMT

Thank you, it works!

Re: Why can I only paste 4000 rows in LOAD INLINE?

jmvilaplanap — Tue, 11 Jul 2017 16:07:34 GMT

Then you must to filter the source not to create an INLINE table.

Did is wrong the data in the source? Can you separate the second field?

Re: Why can I only paste 4000 rows in LOAD INLINE?

juraj_misina — Tue, 11 Jul 2017 16:12:09 GMT

You could still do this directly from MySQL, simply connect to the source (via ODBC) and replace to LOAD * INLINE with respective SQL statement. No need to copy/paste stuff.