topic Re: Set Analysis Issue : Compare Fields between two tables in QlikView

Set Analysis Issue : Compare Fields between two tables

Fri, 20 Feb 2015 06:32:57 GMT

T1.Year and T1.Month Field is available in Table1

T2.Year, T2.Month and T2.Budget is available in Table 2

Now, These tables are not linked with each other. T1.Year and T1.Month are the filters in the Dashboard. If no Year and Month is selected the expression should give me sum of all Budget for the Year and Month present in Table1. If a Year or Month is selected Table2 should fetch the Budget only for the selected Year and Month.

Expression Used:

sum( {<T1.Year={"=T1.Year=T2.Year"}, T1.Month={"=T1.Month=T2.Month"}, >} T2.Budget)

Can anybody please help me with a suitable solution for this

Re: Set Analysis Issue : Compare Fields between two tables

jonathandienst — Fri, 20 Feb 2015 07:21:15 GMT

I hope your data set is very small, otherwise this structure will perform poorly or not at all. Expressions with fields from unassociated tables require QV do a cartesian join between the two tables. For example, if they each contain 10,000 rows, the join will be 100 million rows.

Either create a selection in the budget table, concatenate T1 and T2, or join the tables by using common Year, Month fields (or a YearMonthKey if you don't want a synthetic key)

HTH

Jonathan

Re: Set Analysis Issue : Compare Fields between two tables

timanshu — Fri, 20 Feb 2015 08:43:05 GMT

If T2 contains Month and Year , then why don't you use these fields as filter??

If you still want Sum of budget to be filtered on T1.Year and T1.Month selection , then you can rename T2.Year and T2.Month as T1.Year and T1.Year respectively.

Re: Set Analysis Issue : Compare Fields between two tables

petter — Sun, 26 Jul 2015 10:56:50 GMT

The field on the left-hand-side of the equal sign has to be a unique identifier for the table in question or the the comparison will fail. So you don't need to repeat the T1.Year you should rather write T1.ID (or whatever field is a unique identfier).

As Jonathan points out this might be a very costly operation when it comes to performance - so be careful.

Sum( { <T1.ID={ "=T1.Year=T2.Year AND T1.Month=T2.Month" } > } T2.Budget )