topic Re: [Logical Problem] Chat with all possible combinations in QlikView

[Logical Problem] Chat with all possible combinations

Wed, 05 Feb 2014 14:36:26 GMT

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?

Re: [Logical Problem] Chat with all possible combinations

luis_pimentel — Wed, 05 Feb 2014 15:18:22 GMT

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 ;(

Re: [Logical Problem] Chat with all possible combinations

Carlos_Reyes — Wed, 05 Feb 2014 15:43:50 GMT

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;

Re: [Logical Problem] Chat with all possible combinations

Wed, 05 Feb 2014 16:33:29 GMT

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

Re: [Logical Problem] Chat with all possible combinations

jerem1234 — Fri, 07 Feb 2014 14:37:46 GMT

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!