Using a dimension value in set analisys

maximiliano_vel — Thu, 24 Dec 2015 16:42:28 GMT

Hello,

I have two tables that are not linked.

Table1:

Col1	Col2	MatchCod
A	A1	'4102','4110'
A	A2	'4104','4107','4108*'
A	A3	'4105','4106'
B	B1	4101*'
B	B2	'4115*'
B	B3	'4117','4119'

Table2:

Cod	Value
4101	2424
410101	3190
410102	1718
410102001	1770
4102	2175.00
410202	2133.00
4103	3236
4104	2294
410404	3455
4105	3371
4106	1854
4107	1718
410701	3666
4108	3481
410801	3060
410801001	2408
4109	1682
4110	3817.00
4111	3446
4112	2511
4113	3746
4114	3400
4115	2070
4116	3295
4117	3388
411701	3157
41171001	3194
4118	2267
4119	3101
4120	2617
4121	2943
4122	1620

I need to create a pivot table so the results depends on the MatchCod Dimension. Something like

=Sum({<Cod={$(=Only(MatchCode))}>} Value)

The results should be:

Col1	Col2	MatchCod	=Sum({<Cod={$(=Only(MatchCode))}>} Value)
A	A1	'4102','4110'	8125
A	A2	4104','4107','4108*'	20082
A	A3	'4105','4106'	5225
B	B1	4101*'	9102
B	B2	'4115*'	2070
B	B3	'4117','4119'	12840

The formula above, only works when i select an specific row, I need this to work with no selections.

Please advice

Regards

Re: Using a dimension value in set analisys

Peter_Cammaert — Thu, 24 Dec 2015 16:55:45 GMT

That's not the way to do it, because a data set specified by set analysis will be assembled before the object (a table in your case) is recalculated. The result will be that in the set analysis spec, Only(MatchCode) will try to return all active MatchCode values, which results in a NULL value.

If a single value in a table has to match a code range, why not do this in your script using WildMatch() ?

topic Re: Using a dimension value in set analisys in QlikView

Using a dimension value in set analisys

Re: Using a dimension value in set analisys