QlikView S2 Loading times/joins

Wed, 03 Sep 2014 17:08:19 GMT

Wondering if someone can give me some advice please.

Some of the joins in my QlikView S2 document seem to be taking a fair amount of time, i'm not very experienced in QlikView so wondered if this is usual or not?

For example:

1 - I have one table where i am loading 56 fields (from an optimised QVD), there are 1,654,422 lines and its taking approximately 5 minutes to load, is that usual?

2 - I have a join that is essentially loading 900,000 lines, using an if statement to create a new field and then it joins these back to the original table in 11 seconds.

I have another join which has a similar if statement to add the field but QlikView seems to hang at that point in the script, i've not successfully managed to get past that point as it hangs until it fails. Both of these joins are joining back to the original table so there are no many to one/grouping issues. I suspected that something was wrong with my If statement but i have tested that using a text box and its working perfectly, below is an example of the join and relevant log entry:

left Join (Table1)
load UniqueID,
NetWorkDays(FLOOR(date1),date2,$(vHoliday)) as ResolutionDays
Resident Table1

//(note date 2 already held as a date only (no time) so floor is not being used)

03/09/2014 16:30:08: Joining/Keeping
03/09/2014 16:30:16: 2127 left Join (Table1)
03/09/2014 16:30:16: 2128 load Unique_ID,
03/09/2014 16:30:16: 2129 NetWorkDays(FLOOR(date1),date2,'25/08/2014','26/05/2014','05/05/2014','21/04/2014','18/04/2014','01/01/2014','26/12/2013','25/12/2013','26/08/2013','27/05/2013','06/05/2013','01/04/2013','29/03/2013','01/01/2013','26/12/2012','25/12/2012','27/08/2012','28/05/2012','07/05/2012','09/04/2012','06/04/2012','02/01/2012','27/12/2011','26/12/2011','29/08/2011','30/05/2011','02/05/2011','25/04/2011','22/04/2011','03/01/2011','28/12/2010','27/12/2010','30/08/2010','31/05/2010','03/05/2010','05/04/2010','02/04/2010',) as ResolutionDays
03/09/2014 16:30:16: 2130 Resident Table1
03/09/2014 16:30:16: 2131

03/09/2014 16:30:16: 2 fields found: Unique_ID, ResolutionDays, 3,613,492 lines fetched

Nothing else happens after the line above and eventually the reload fails.

3 - i have a single table (not QVD optimised but an in line load), this is loading 3 lines but takes 2 minutes, this doesnt seem right considering some of the times i have given above.

Can anyone provide any advice as to why this is occuring and how to approach joins that cause a complete time out or join very slowly? I would also like to get a feel from others to see if each of the above is the 'norm' or not in terms of loading time. I would also be interested to know if the prescence of synthetic tables in the data model when performing joins would have a detremental impact? The data model in question was handed over to me and certain synthetic tables do exist during the load however i am very limited to where abouts i can script the flags i wish to add because the relevant fields/tables need to be loaded first. Also due to the denormalised data model some of those synthetic tables cannot be removed until later in the loading script.

Just so you know the steps i have taken already i usually:

1. Test the code i am adding in a text box first to ensure i get the desired result

2. If using where clauses to reduce the number of records being loaded i replicate these on a development sheet so that i know how many records i should be expecting

3. Run the script with small limited load first to ensure the code is ok and that there are no issues

; Thank you

Re: QlikView S2 Loading times/joins

Anonymous — Wed, 03 Sep 2014 17:24:52 GMT

I am not sure what QlikView S2 is. Is it QlikSense or QlikView ?

Could you share the load script and the complete log file ?

Re: QlikView S2 Loading times/joins

Wed, 03 Sep 2014 18:20:30 GMT

QlikView S2 is the QVD that transforms my raw data into a denormalised reporting data model.

Re: QlikView S2 Loading times/joins

Wed, 03 Sep 2014 18:27:36 GMT

Sorry i can't attach the log file. Guess i just wanted to prompt a general debate as to common causes

Re: QlikView S2 Loading times/joins

rwunderlich — Wed, 03 Sep 2014 21:13:23 GMT

It's tough to offer much specific help without the document log. But here goes.

#1 & #3. Those load times seem slow. Is that INLINE load the last load?

#2.

"Both of these joins are joining back to the original table so there are no many to one/grouping issues"

That is not necessarily true. Could be if both sides of the join have a primary key. Have you confirmed the record counts?

You mention an if() statement, but I don't see an if() in what you posted?

It would be interesting to see both join1 and join2. If they are both self joins (source and target table the same), could they be done in a single join?

-Rob

Re: QlikView S2 Loading times/joins

Thu, 04 Sep 2014 12:38:18 GMT

#1 is not an inline load, its from an optimised QVD

#3 is not an inline load, but its from a non optimised QVD

Both #1 and #3 are only loads, not joins

#2 is a self join - record count confirms a 1-1 relationship. Both joins i mention in this point are to different tables so cannot be scripted into a single join

Any ideas what would cause the loading script to hang when processing a join?

Thanks

topic Re: QlikView S2 Loading times/joins in QlikView

QlikView S2 Loading times/joins

Re: QlikView S2 Loading times/joins

Re: QlikView S2 Loading times/joins

Re: QlikView S2 Loading times/joins

Re: QlikView S2 Loading times/joins

Re: QlikView S2 Loading times/joins