Why can I only paste 4000 rows in LOAD INLINE?

[#1]

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

[oknotsen]

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

[juraj_misina · Accepted solution]

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

[jmvilaplanap]

Hi

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

[#5]

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"

];

[#6]

Thank you, it works!

[jmvilaplanap]

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?

[juraj_misina]

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.