Join tables in script or view

[qliktowin]

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!

[qliksus]

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 

[jonathandienst]

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.

1. If the calc is simple, and would only need to be done a few times, i would do it in the front end.
2. 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.
3. 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.

[qliktowin]

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!!

[qliktowin]

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!!

[qliksus]

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

[qliktowin]

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!!

[qliksus]

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 .

[Brett_Bleess]

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