Hi
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
