Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
tonneofash
Creator
Creator

.TXT file upload interrupted, loads to application incomplete

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?  

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

14 Replies
Anil_Babu_Samineni

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tonneofash
Creator
Creator
Author

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?

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tonneofash
Creator
Creator
Author

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?

Anil_Babu_Samineni

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'

OR

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tonneofash
Creator
Creator
Author

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.

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tonneofash
Creator
Creator
Author

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

Anil_Babu_Samineni

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

What was the 'B&Q' here?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)