topic Join tables in script or view in QlikView

Join tables in script or view

qliktowin — Sat, 16 Nov 2024 01:12:49 GMT

Hi All,

I have a doubt regarding performance. I have to create a field (Field c = field a * field b) which is a multiplication of 2 fields (field a from table 1 and field b from table 2) present in different tables.

Now, should I join the 2 tables while loading them in the script, or keep them seperate and create the field (field c) in the viz while creating chart. Also, I would like to mention that field c will be used in multiple charts.

Table 1 has 264 rows and 52 columns. Table 2 has 2310 rows and 18 columns.

Thanks!

Re: Join tables in script or view

qliksus — Mon, 24 Feb 2020 08:54:21 GMT

Considering the number of rows you have , you can keep the two tables separate by having the Qlikview make the join instead of using the join keyword and resident load for creating this new field . Just create a variable in the front end

vField c = field a * field b

and use the vairble in all the places you want

Re: Join tables in script or view

jonathandienst — Mon, 24 Feb 2020 09:24:20 GMT

This being a small data set, performance is not important in determining whether to do the calc in the load or in the front end.

If the calc is simple, and would only need to be done a few times, i would do it in the front end.
However, if the the derived field calculation would need to be done in many objects in the front end, and/or is a complex calculation, I would consider doing it in the load script.
Remember that a join runs the risk of duplicating data if the join fields contain duplicates. This is not usually a problem in the front end.

Re: Join tables in script or view

qliktowin — Mon, 24 Feb 2020 15:10:38 GMT

Hi Qliksus,

I tried this but was not working. The actual logic I am implementing is:-

field C = If(Field a = 'XYZ', field b * field d, 0)

Field a and b are from Table 1 and field d is from table 2. I put the logic of field c in variable (varc) and used it in the chart. The chart had Sum($(varc) + $(variable)). variable is just another variable present in my document. Can you please tell me what am I doing wrong.

Thanks!!

Re: Join tables in script or view

qliktowin — Mon, 24 Feb 2020 15:17:54 GMT

Hi Jontydkpi,

I am new to this and not exactly sure which calculations are considered complex calculations. I have just put the exact logic I am trying to implement right above, if you want to take a look at it. I certainly dont want to join in the script because one table is a dimension and other is a fact.

Hence, what do you think I should do. Please let me know if this is confusing and doesn't make sense. I would try my best to explain.

Thanks!!

Re: Join tables in script or view

qliksus — Tue, 25 Feb 2020 04:55:43 GMT

Are you hetting values for just sum( $(varc)) ? whats the formula in Variable ? can you attach the file to check ?

Re: Join tables in script or view

qliktowin — Tue, 25 Feb 2020 19:00:10 GMT

Hi Qliksus,

i cannot attach the file but let me add more details to the whole scenario.

Table_1 has many fields. 2 of them are: Field_A, Field_B. Similarly Table_2 has many fields and one of them is Field_C. Both the tables are present in SQL SERVER database and I am loading them from there. Below is the whole logic and how everything is interacting. The logic for 2 variables is:

Var_A = If(Field_A = 'XYZ', field_B * field_C, 0)

Var_B = If(Field_A = 'PQR', field_B * field_C * 12, 0)

'XYZ' and 'PQR' are just 2 different strings in my data based on which the field is calculated. Var_A and Var_B will be used multiple times and thats why I am keeping the logic into 2 variables and not one.

In the chart, in the expression I have put: Sum($(Var_A) + $(Var_B))

This is exactly how the things are present in the document. I hope it is clear. Please let me know if this is confusing.

Thanks!!

Re: Join tables in script or view

qliksus — Wed, 26 Feb 2020 08:58:12 GMT

Hope you are not using any aggregation within the variable like " If(Field_A = 'XYZ', sum(field_B * field_C), 0) " ? .

Are you getting values for the expression sum(If(Field_A = 'XYZ', field_B * field_C, 0)) ? If not then you dont have data for the filter you are doing .

Re: Join tables in script or view

Brett_Bleess — Thu, 26 Mar 2020 12:00:06 GMT

I would recommend having a look over the following Design Blog along with the related posts in that one, as that will likely give you the underlying knowledge for which you are looking regarding the 'costs' of doing things in different ways, I am including the base URL to the Design Blog area too in case you wish to search further on you own, but I hope this will allow you to learn the details of things work behind the scenes.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Calculation-Engine/ba-p/1463265

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett