topic Re: loading a large CSV document - and a lookup file - which method is faster? in QlikView

loading a large CSV document - and a lookup file - which method is faster?

josephinetedesc — Mon, 21 Nov 2016 04:03:01 GMT

Hi All

a. I have made a QVD document which loads a large csv file of 1,352,205 records

b. I then load a much smaller table - this is the "lookup table" for the first file. I use "join" and the large file becomes a large single file

When I make a selection in qlikview - it is quite slow and it shows the"loading tool"

I could load the 2 files separately and just let qlikview create a synthetic join.

My question is would be the faster load? I think the join is marginally faster? Any advantages either way?

Thank you

Re: loading a large CSV document - and a lookup file - which method is faster?

marcus_sommer — Mon, 21 Nov 2016 06:24:28 GMT

I assume that your join creates a lot of records because of a n:m relation between the tables and it would be helpful if you provide the used script, a few sample data and a screenshot from the tableviever.

- Marcus

Re: loading a large CSV document - and a lookup file - which method is faster?

stabben23 — Mon, 21 Nov 2016 07:33:48 GMT

Hi Jo,

if you do a separatly reload and got synthetic keys, than you will have problem to do a JOIN.

If you want to Join, use all "common" Fields from the synthetic table and create a composite key With all these Fields.

ex Field1 & Field2 & Field3 as %Key, do this in both tables.

But one thing: Do you need to JOIN? A normal assosiation should work and save a lot of memory for you.

Re: loading a large CSV document - and a lookup file - which method is faster?

josephinetedesc — Mon, 21 Nov 2016 21:56:34 GMT

Thank you Staffan and Marcus

I can either use a join or not explicitly use the join - with possible results being

Are you suggesting Staffan that I should make a composite key TestSet & TestName in both the BI_misc table and the Pathology table and this would make the load faster?

It is not just the load - this is fairly fast, it is the moving between sheets that causes the loading tool to appear.

Re: loading a large CSV document - and a lookup file - which method is faster?

vishsaggi — Mon, 21 Nov 2016 22:20:51 GMT

If you are trying to do a lookup table, why cant you use Mapping load and Applymap()?

And create a Keyfield in your calendar and pathology table like:

Num(Floor(dateReceived)) As [%Key Date],

dateReceived As PathologyDate

-> Any specific relation between Pathology and BIMisc table are they joined with any specific ID's? Coz your syn table field names show TestSet and TestName, if they are not relevant try renaming the fields to remove synthetic keys?

As Marcus mentioned try and share your script that builds your data model for more suggestions for us to give.

Thanks,
V.

Re: loading a large CSV document - and a lookup file - which method is faster?

rwunderlich — Mon, 21 Nov 2016 22:47:29 GMT

From what you describe, it's likely not the data model that is causing you performance problems. but rather the chart expression(s).

Can you post;

1) A tablebox that contains the fields $Tables and $Rows.

2) A screenshot of your chart, plus the expressions used in the chart.

-Rob

Re: loading a large CSV document - and a lookup file - which method is faster?

josephinetedesc — Mon, 21 Nov 2016 23:04:58 GMT

Hi Rob - this is what you meant?

where the loading tool comes up is mainly in an ordinary table box - which has all fields.

I suppose what this means is that the table is doing a lookup table operation 4 times for each record in order to add the fields from the BI_misc table - this means going through a lot of records - hence the loading tool.

result when using "join"

or result not using join - just the synthetic table

Re: loading a large CSV document - and a lookup file - which method is faster?

rwunderlich — Mon, 21 Nov 2016 23:41:47 GMT

I was looking for the row count for each table.

-Rob

Re: loading a large CSV document - and a lookup file - which method is faster?

josephinetedesc — Mon, 21 Nov 2016 23:49:10 GMT

this one? for both scenarios?

TableName	Loose	#Records	#Fields	#Keys
Pathology	0	1352205	14	2
BI_misc	0	1680	5	1
MasterCalendar	0	732	12	1

Re: loading a large CSV document - and a lookup file - which method is faster?

stabben23 — Tue, 22 Nov 2016 07:20:05 GMT

Hi Jo,

The "loading tool" could be there if you do Your developing at Your local computer (Laptop). The memory is to low.

If you have the problem sitting on a qlikview server then its probably as rwunderlich‌ mention, it caused by Your Charts.

Re: loading a large CSV document - and a lookup file - which method is faster?

stabben23 — Tue, 22 Nov 2016 07:29:23 GMT

Yes Jo, here is best practice to use TestSet & TestName as a composite key.

Have you checked that dateReceived is Connected With the same format?

Re: loading a large CSV document - and a lookup file - which method is faster?

marcus_sommer — Tue, 22 Nov 2016 08:01:41 GMT

It's not clear if a join will work properly but you could avoid the potential problems of joins if you used mapping: Mapping as an Alternative to Joining.

But for me it didn't look necessary to merge these tables - an association between them within the datamodel should work (even with a synthetic key which is technically nearly the same like a combined key) so that I think that the poor performance will be caused from your objects and expressions. How do they look like?

- Marcus

Re: loading a large CSV document - and a lookup file - which method is faster?

josephinetedesc — Tue, 22 Nov 2016 21:48:40 GMT

Hi Staffan

this might be the second time I reply - I cannot see my previous reply.

dateReceived is shown as as a date with format dd/MM/yyyy however in the script creating the calendar as shown below it would be a number. I am working on my local computer. I have not checked what it looks like from the server.

Temp:

Load

min(dateReceived) as minDate,

max(dateReceived) as maxDate

Resident Pathology;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Re: loading a large CSV document - and a lookup file - which method is faster?

josephinetedesc — Tue, 22 Nov 2016 22:05:38 GMT

"The only time that you cannot use Applymap() instead of a join is if you want to fetch more than one single corresponding value (record) from the second table."

It also seems to mean that I will need to create 4 Applymap()

So my next step I think will be to check this out on the server ... see if it is slow for the users.

Thank you all for your help.

Re: loading a large CSV document - and a lookup file - which method is faster?

marcus_sommer — Wed, 23 Nov 2016 05:58:09 GMT

You could use concatenated fields within an applymap() and split them afterwards again with a subfield(), like:

map:

mapping load key, F1 & '|' & F2 & '|' & F3 from mapSource;

applymap:

load

subfield(applymap('map', key, '#NV'), '|', 1) as F1,

subfield(applymap('map', key, '#NV'), '|', 2) as F2,

subfield(applymap('map', key, '#NV'), '|', 3) as F3

from source;

- Marcus

Re: loading a large CSV document - and a lookup file - which method is faster?

stabben23 — Wed, 23 Nov 2016 06:48:50 GMT

"Outside this discussion" Then I learn something New, the concatenated applymap is a great tool for the Developer toolbox, thanks marcus_sommer‌