topic Re: Join / Merge actual and target values in QlikView

Join / Merge actual and target values

konHi — Fri, 05 Jun 2015 12:25:53 GMT

Hi,

i have the following problem and don't see the solution how to solve it (tried a lot of things but still don't get it).

I have two tables:

1: actual sales

2: targets

Actual Sales:

RowNr.	Dep.	Main. Dep	Area	Plant	Modell	Sales
1	A	BB	CDE	1	-	5
2	A	BB	CDF	1	-	5
3	A	BB	FDE	1	-	12
4	A	BB	FDE	2	-	5
5	B	BB	FDE	-	-	16
6	A	BC	-	-	Mod1	17

2: Target

RowNr.	Dep.	Main. Dep	Area	Plant	Modell	Target
11	A	-	-	-	-	12
12	A	BB	-	-	-	8
13	-	BB	-	-	-	9
14	A	BB	FDE	-	-	13
15	-	-	-	-	Mod1	5
16	-	BC	-	1	-	8
17	A	-	-	1	-	12

As a result I want to have following in Pivot Table:

RowNr.	Dep.	Main. Dep	Area	Plant	Modell	Sales	Target
21	A	-	-	-	-	44	12
22	A	BB	-	-	-	27	8
23	-	BB	-	-	-	43	9
24	A	BB	FDE	-	-	17	13
25	-	-	-	-	Mod1	17	5
26	-	BC	-	1	-	0	8
27	A	-	-	1	-	22	12
28	A	BB	-	1	-	22	-
29	-	BC	-	-	Mod1	17	-

There is no real hierarchy in the structure. I always want to have the sum(Sales) and the Target at the specific level. (RowNr. is only for reference. )

For Example:

Example RowNr:	Pivot Dimensions	Sales Expression	Target Expression
22	Dep. -> main.Dep: A->BB	=sum(Row1+Row2+Row3+Row4) = 27	=Target from Row 12 = 8
24	Dep. -> main.Dep ->Area: A->BB-FDE	=sum(Row3+Row4) = 17	=Target from Row 14 = 13
26	Main.Dep ->Plant	=sum(Not Available) = 0	=Target from Row 16 = 8
28	Dep. -> Main.Dep. -> plant	0sum(Row1+ Row2 + Row3) = 22	=Target now available = -

I hope you understand my problem. I am very grateful for you answers. Please find attached the QVW with the data. thank you very much

Re: Join / Merge actual and target values

Anonymous — Fri, 05 Jun 2015 13:34:51 GMT

Have you tried to concatenate Sales and Target?

See attached

Re: Join / Merge actual and target values

konHi — Fri, 05 Jun 2015 15:32:41 GMT

Hi Michael,

thank you for the answer. The problem is, I am not allowed to aggregate the target through sum. I have to pick the right target at the right level. In you app, i get for Dep: A Target 24 or 45. But I should pick the RowNr21 and show as Target: 12.

Re: Join / Merge actual and target values

Colin-Albert — Fri, 05 Jun 2015 16:05:50 GMT

Is there another field that can be used to associate the rows you are summing for the Sales value and the Target rows instead of trying to associate the data by row numbers. - A WeekNo, or Period//Year would be the ideal option.

If you could concatenate the Sales & Target data , then you could simply sum the Sales and Target data against a Period field, to give the chart you are looking for.

Concatenating Sales and Target data is the simplest solution to ensure that you do not have an incorrect join causing target values to be counted multiple times.

Re: Join / Merge actual and target values

sunny_talwar — Fri, 05 Jun 2015 16:10:04 GMT

You are not going to like this, but it kind of does the job for you:

=If(Len(Trim(Main.Dep)) = 0 and

Len(Trim(Plan)) = 0 and

Len(Trim(Modell)) = 0 and

Len(Trim(Area)) = 0, Sum(TOTAL <Dep.> Sales),

If(Len(Trim(Dep.)) = 0 and

Len(Trim(Plan)) = 0 and

Len(Trim(Modell)) = 0 and

Len(Trim(Area)) = 0, Sum(TOTAL <Main.Dep> Sales),

If(Len(Trim(Dep.)) = 0 and

Len(Trim(Plan)) = 0 and

Len(Trim(Main.Dep)) = 0 and

Len(Trim(Area)) = 0, Sum(TOTAL <Modell> Sales),

If(Len(Trim(Plan)) = 0 and

Len(Trim(Modell)) = 0 and

Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Main.Dep> Sales),

If(Len(Trim(Main.Dep)) = 0 and

Len(Trim(Modell)) = 0 and

Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Plan> Sales),

If(Len(Trim(Modell)) = 0 and

Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Main.Dep, Plan> Sales),

If(Len(Trim(Modell)) = 0 and

Len(Trim(Plan)) = 0, Sum(TOTAL <Dep., Main.Dep, Area> Sales),

If(Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Main.Dep, Plan, Modell> Sales), Sum(Sales)))))))))

You may need to add more If statements here, but it kind of get the output you are looking for.

Attaching the qvw for reference as well.

Best,

Sunny

Re: Join / Merge actual and target values

konHi — Mon, 08 Jun 2015 07:37:31 GMT

Hi Sunny,

it seems to work. quit complicated but my first testing was positive. Thank you very much!!!

I will keep you up to date.

Have a nice day

[edit]: ist that right, that i will have 2^[number of fields] possibilities for the if clause? At the end i will have 12 Fields with targets in every possible combination = 2^12 = 4096

Re: Join / Merge actual and target values

sunny_talwar — Mon, 08 Jun 2015 10:00:36 GMT

Yup and that was the reason I said that you are not going to like this formula. Am sure there must be a better way to do this, but I just don't know how to do it

Best,

Sunny

Re: Join / Merge actual and target values

konHi — Tue, 09 Jun 2015 08:33:23 GMT

Hi Sunny,

I tried to make the expression dynamic. Find the example attached. The "String" in the concatenated row seems to work but the evaluated row does not evaluate the string properly. Do you see any mistakes?

Thank you,

Konstantin

Re: Join / Merge actual and target values

sunny_talwar — Tue, 09 Jun 2015 10:59:28 GMT

I really liked the idea of what you are doing, but I think the problem is that the expression is always evaluated as false and giving you an output for the false statement which is Sum(Sales). Take a look at the image below:

We somehow need to make it to evaluate the if statement and give an output. But I really liked the idea of what you have done.

Best,

Sunny

Re: Join / Merge actual and target values

sunny_talwar — Tue, 09 Jun 2015 11:12:30 GMT

I kind of understand the problem now. Your evaluated Sales new is getting evaluated at the chart level instead of row level. The way I was able to figure this out was by selecting Main.Dep BB and seeing the result as 43 instead of 0 when everything else was null (See the image below)

Re: Join / Merge actual and target values

konHi — Thu, 11 Jun 2015 15:05:45 GMT

Thank you, after trying to figure out how to force qlikview to evaluate at row level I am still not getting any progress. I tried to evaluate a dynamic set analysis in an expression. Still not getting what i want (set analysis is evaluated on the chart level too)

The problem should not be that difficult but I don't see the path to solve the problem.

Any other suggestions? I tried to do it with "generic keys", no progress 😞

Re: Join / Merge actual and target values

konHi — Tue, 16 Jun 2015 15:13:12 GMT

My new approach was to create two fact tables and in Expression:

=only(if(

if( vShowDep=1, Dep.=T_Dep., T_Dep. = '')

and (if( vShowMainDep=1, Main.Dep=T_Main.Dep, T_Main.Dep = ''))

and (if( vShowArea=1, Area=T_Area, T_Area = ''))

and (if( VShowModell=1, Modell=T_Modell, T_Modell = ''))

and (if( vShowPlan=1, Plan=T_Plan, T_Plan = ''))

, T_Target))

it works, but for the calculation QlikView creates an cartesian product (in the background) very very bad performance. Any idea in tuning this?

Re: Join / Merge actual and target values

Colin-Albert — Tue, 16 Jun 2015 15:29:31 GMT

I think you need to re-evaluate the data model and the table structure of your data.

Consider concatenating your actuals and target data as initially suggested by mov‌ and others.

Re: Join / Merge actual and target values

konHi — Tue, 16 Jun 2015 15:38:38 GMT

Hi Colin, thank you for you answer.

The problem is, a simple concatenation and then sum(Target) does not work, because qlikview sums all targets with Dep = A for example. but it should only show the target where Dep = A and all other fields are empty. If i Want to show Dep. and Main.Dep then only the Target where dep. and Main. Dep has values should be shown.

Re: Join / Merge actual and target values

Colin-Albert — Tue, 16 Jun 2015 15:52:02 GMT

If your data has a common Dep dimension field on the actual and target records, then having the Dep as a dimension of your chart will ensure that sum(Target) will show the relevant data for each Dep.

You will need to have relevant dimensions on both the actual and target records.

Ignore the row numbers and analyse your data by the dimensions.