topic Re: Multiplication of fields values in script in QlikView

Multiplication of fields values in script

Sat, 20 Feb 2016 03:28:21 GMT

i experts,

I have a scenario where in a table I have two fields named as Amount and resid_tnor which have negative values of 4 to 5 digits each. As per the requirement the script is written in such a way that it filters some data from multiple fields and concatenated that new data to resident table again by adding a hardcoded field value into a new field for that particular filteredvalue. Also while filtering there is a calculation in of Amount*resid_tnor field which I have mention at the first. When I reload the script it is taking 6-8minutes of time to load only 2million records. I want to optimize this reload time. Please help. I am attaching the main table and subsequent code of concatenation. Please do help me. I have to solve this issue asap. The values of fields are like this example,

Re: Multiplication of fields values in script

Gysbert_Wassenaar — Sat, 20 Feb 2016 10:31:52 GMT

I don't think you can optimize it a lot. Some things you can try.

Load only the data into SecFund_Full that you really need to create SF_SUFA. It looks like you can move the check len(product_group)>0 to this load. Perhaps you can filter the classifications and triparty_bilateral_ind here too.
Store SecFund_Full into a qvd and load the data for SF_SUFA from this qvd instead of using a resident load.
Combine some of the loads into one. The Reverse Repo and Repo Bilateral look like a good candidate. I think you can do that in one load if you change the where clause to include both classifications and triparty_bliateral_ind values.
Check the cost of calculating joins and synthetic keys if there are any
Get faster hardware

Re: Multiplication of fields values in script

Sat, 20 Feb 2016 12:58:59 GMT

But I would Like to know, by dropping the calculation of WAMNumerator field while loading, and doing it at the end by using resident load for all table at once, will it make any difference?

Also by this multiplication fields, we are storing a value of more than 7 digits in table. but the actual optimized limit is 8bytes in a field. this is where I want to emphasize more. Just want know whether this can make any difference in terms of loading time?

Re: Multiplication of fields values in script

Gysbert_Wassenaar — Sat, 20 Feb 2016 15:46:30 GMT

I doubt that moving the calculation elsewhere will make a difference. If you want to know for sure then you'll have to test both options.

Also by this multiplication fields, we are storing a value of more than 7 digits in table. but the actual optimized limit is 8bytes in a field

That's not correct. See this blog post for the reason why: Symbol Tables and Bit-Stuffed Pointers

Re: Multiplication of fields values in script

Sat, 20 Feb 2016 16:58:53 GMT

Thank you for the support.

I also have a requirement of making a total Column in pivot table which calculates the sum of all the expressions for all rows. All expressions calculates the sum of amount based on the bucket. I want to make this available for row totals too. but sum of rows option is not enabled for me