[Logical Problem] Chat with all possible combinations

[#1]

I have a table with values and codes of transactions. I need to find all possible combinations of the sum(values) which will give me a fixed number.

For example, I have these values: 2,4,4,6,7,3,5 and the fixed number is 10:

I need to show in a dynamic table the combination of possible sum(values) that will give the number that I want, but there's more than one combination, so I'll have to create groups with those combinations.

In this case, 4 groups: 1st (2,3,5) ; 2nd (4,6); 3rd (4,6); 4th (7,3);  ==> 2+3+5=10 ; 4+6=10 ; 7+3=10

That's what I have:

|   cod:  |   Vlr   |  

|   100   |    2    |

|   101   |    4    |

|   102   |    4    |

|   103   |    6    |

|   104   |    7    |

|   105   |    3    |

|   106   |    5    |

Variable: vFixed = 10

And that's what I need:

This table was created only as an example of the result that I'm looking for.

Can someone help me?

[luis_pimentel]

Hi Rodrigo,

I can not think on how doing what your are looking for in the script or the expressions. You probably should try with a macro.

Sorry ;(

[Carlos_Reyes]

Well.. as Luis says.. I don't think you can get that result in an object. I don't know about macros because I never use those... so assuming that all possible combinations can be from 2 or three different values I'd try to create all possible combinations and valid results in the script. Try the next:

LET vFixed = 10;

Combinations_temp:
LOAD Distinct
Cod, Vlr
FROM YourSource;

JOIN
LOAD Distinct
Cod      as Cod2,
Vlr        as Vlr2,
2 as Flag_2Values
Resident Combinations;

CONCATENATE
LOAD Distinct
Cod,
Vlr,
Cod2,
Vlr2,
3 as Flag_3Values
Resident Combinations;

JOIN
LOAD Distinct
Cod      as Cod3,
Vlr        as Vlr3,
3 as Flag_3Values
Resident Combinations;

Then, you'll have to check which rows create a valid match with your variable.

Combinations:
NoConcatenate
LOAD DISTINCT
Cod,
Vlr,
Cod2,
Vlr,
Cod3,
Vlr3
Resident Combinations_temp
WHERE
(RangeSum(Vlr,Vlr2)=$(vFixed) and Flag_2Values=2)
or
(RangeSum(Vlr,Vlr2,Vlr3)=$(vFixed) and Flag_3Values=3)
;

Drop table Combinations_temp;

[#4]

First of all thanks for bringing this in Qlikview. May be Qlikview experts can give a Qlikview solution, but what i have for you is a SQL server solution.

This type of problem is known as Knapsack Problem. Now if you execute the below script in SQL you get the desired results:

create table #x

(

   id    int not null primary key,

   price numeric(12,4) not null

);

INSERT INTO #x

  (id, price)

VALUES

  (100,2),

   (101,4),   

   (102,4), 

   (103,6),   

   (104,7),   

   (105,3),   

   (106,5);

  with calc as (

   select id as id,

          cast(id as varchar(max)) as id_list,

          cast(price as numeric(14,2)) as price

   from #x

   where price <= 10

   union all

   select c.id,

          p.id_list+','+ cast(c.id as varchar(max)),

          cast((c.price + p.price) as numeric(14,2))

   from #x as c

     join calc as p on p.id < c.id

   where c.price + p.price <= 10

)

select *

from calc

where price = 10

order by id_list

drop table #x

[jerem1234]

Have a look at attached. The following will give you all the subsets (except the empty set, but that can be added if need be) of your set Vlr. Not the prettiest of things, but it does the job. It also finds the total sum for each set, so you can compare in your chart what sum you are looking for. The only problem with this is that it won't list duplicate subsets, so it will only give one group (for example 4,6 will only be listed once). Also, it uses loops, so if your set is huge, this will have affect on how long the script takes.

Used the calculated dimension:

=if(Vlrsum =vSum,  Subset)

where vSum is the variable sum you want.

Hope this helps!