topic Re: .TXT file upload interrupted, loads to application incomplete in QlikView

.TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 08:58:07 GMT

Hi all, I have a very odd issue with my data in Qlik.

I've been trying to import a text file, 2GB in size, from my local drive into Qlik (to put it through the whole data, extract, standard process). But when I import the cleansed, standardised file into my application, it's only 54MB. It's missing most of the data.

I've tried it on a completely new Qlik file, just with the import code and nothing else; no 'where' clauses, nothing. But it still only loads a small amount of the data (1,021,413 records).

Any ideas what can be causing this?

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 09:18:14 GMT

Create one variable with using of NoOfRows() function

Table-Directory:

Load * From Table;

LET vRows = NoOfRows('Table-Directory');

Then create Text object with the value of

=$(vRows) // See how may records you see over there

Then, Try samething in DB then check whether two sources are coming same Or not

Select *, Count(*) as 'NoOfRows' from Table;

Re: .TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 09:38:05 GMT

Cheers, I'll try that.

I mean, I'm fairly sure about how many rows are loaded into the application. I put a count on the primary key of the table, and every time I load it, only 1,021,413 rows are loaded (out of about 8 million).

When you say try the same thing in DB, do you mean my DataBase outside of Qlik?

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 09:41:02 GMT

Cool, Then must be limitation issue of .Txt file

Would it possible to share that .Txt file with us?

When you say try the same thing in DB, do you mean my DataBase outside of Qlik?

Yes, I am thinking the same one. Try from DB which out of in Qlik

Re: .TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 09:50:32 GMT

I don't think it would be possible I'm afraid, as it's 2GB in size, and it contains a lot of business-sensitive information (all the competitor information of a large company)

But I've had a look at the DataBase that it comes from (SAS Enterprise Guide query). There is nothing particularly different about the record that it stops at. I've read somewhere that special characters can interrupt the load, but I see none on this record. However, since the txt file is based on a webscrape, i.e. the data is taken directly from websites and dumped into a table, do you think the formatting of the data could be affecting the load?

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 09:56:23 GMT

So, Then how your data looks like. Try something like part - part using where condition.

If you have data from 1990 - 2017 then use initial case like

Load * from Table Where YearField = '1990'

Load * from Table where YearField >= '1990' and YearField <= '2000'

Then use NoOfRows() function to look the Rows

Something like that and then see where this problem occurs.

HTH for this case

ANIL

Re: .TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 10:07:13 GMT

I've tried something similar. There's a field called retailer, the name of the competitor. Currently, it's only pulling in the top 1 million or so rows, so only the first retailer is in the data (Screwfix). I've tried putting in a where clause:

LOAD * FROM <Table> WHERE Retailer = 'Selco'

I know that there are records in that txt with Selco as the retailer, but adding this clause results in 0 rows being fetched.

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 10:10:16 GMT

Qlikview is case Sensitive, I am not sure whether how your field looks

Above you mentioned Simple retailer and where condition you are showing Retailer

LOAD * FROM <Table> WHERE Lower(RetailerFieldNameHere) = 'selco';

Then see how it seems

Re: .TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 10:59:04 GMT

I've tried that, doesn't seem to be any different. Might it be the ampersand in 'B&Q' that's causing the issue?

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 11:07:13 GMT

That i can't help due to you are hiding the Data.

What was the 'B&Q' here?

Re: .TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 11:12:16 GMT

In the retailer field, the next retailer down from Screwfix is B&Q, and that's where the data stops. I'm just now running a query on the original data set to remove the ampersand to see if that works

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 11:14:35 GMT

Try to provide atleast image for that B&Q to track the issue

Re: .TXT file upload interrupted, loads to application incomplete

Anonymous — Wed, 01 Feb 2017 11:23:00 GMT

As you can see, this is the retailer column. The table is in order of Retailer, starting with 'Screwfix' and 'B&Q' proceeds screfix. The ampersand (&) in B&Q may be causing some issues but I'm not sure. Just trying to remove the ampersand now in SAS

Re: .TXT file upload interrupted, loads to application incomplete

Anil_Babu_Samineni — Wed, 01 Feb 2017 11:44:53 GMT

So, Finally your script seems like below

Load *, Retailer from Table;

If so, you could use this for Testing

Load *, Retailer from Table where Retailer = "B&Q";

First try Something like above and if values come across on wall then club above one and below one then see.

Load *, Retailer from Table where Retailer = "B&Q";

Concatenate

Load *, Retailer from Table where Retailer <> "B&Q";

Re: .TXT file upload interrupted, loads to application incomplete

marcus_sommer — Wed, 01 Feb 2017 12:19:20 GMT

At first take a look on the specified file-format for this load and add there no eof and remove a possible msq-entry so that it looked like:

(txt, utf8, embedded labels, no eof, delimiter is '\t')

- Marcus