Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Just for QVW experts.Combinatorial analysis

Hello everyone,

I´ve this simple table:

ITEM

A

B

C

D

Then i need to make this...

ITEM_X

A

AB

AC

AD

ABC

ABD

ACD

ABCD

B

BA

BC

BD

BAC

BAD

BACD

And so on..

Tks

4 Replies
MVP
MVP

Re: Just for QVW experts.Combinatorial analysis

Hi

How's this? Please look at the attachment.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Just for QVW experts.Combinatorial analysis

Ok.. so nice...

But features like 'AA' or 'AAA' or 'BB' is not valid...

Not applicable

Just for QVW experts.Combinatorial analysis

Jonathan....

Another problem.

The table ITEM can be A,B,C,D... OR   A,B,C,D,E,G.... OR A,B...

This table can have a lot of records...

Not applicable

Re: Just for QVW experts.Combinatorial analysis

Hi Joao,

Built a QVW to generate this; see the attachment. Not really optimized though...

One other word of warning: the number of combinations grows very fast once you have a larger number of items in your original list (a bit over 100.000 for 8 items, nearly 1 million for 9 items, nearly 8,5 million for 10 items)!

Regards,

Martijn ter Schegget

The Implementation Group

PS: The script (in case something messes up the attachment):

values:

LOAD * INLINE [

    value

    A

    B

    C

    D

    E

    F

    G

    H

];

tmp_nrofvalues:

LOAD

    count(value) as nrofvalues

RESIDENT values;

LET vNrOfValues = peek('nrofvalues');

DROP TABLE tmp_nrofvalues;

combinations:

LOAD

    null() as combination

AUTOGENERATE (0);

FOR vOuterLoop = 1 to $(vNrOfValues)

    fixed_length_combinations:

    NOCONCATENATE LOAD

        '' as fixed_length_combi

    AUTOGENERATE (1);

    FOR vInnerLoop = 1 to $(vOuterLoop)

        JOIN (fixed_length_combinations) LOAD

            value

        RESIDENT values;

        JOIN (fixed_length_combinations) LOAD

            fixed_length_combi,

            value,

            fixed_length_combi & value as fixed_length_combi_new

        RESIDENT fixed_length_combinations

        WHERE index(fixed_length_combi, value) = 0;

        INNER JOIN (fixed_length_combinations) LOAD DISTINCT

            fixed_length_combi_new

        RESIDENT fixed_length_combinations;

        DROP FIELDS fixed_length_combi, value FROM fixed_length_combinations;

        RENAME FIELD fixed_length_combi_new TO fixed_length_combi;

    NEXT vInnerLoop

    CONCATENATE (combinations) LOAD

        fixed_length_combi as combination

    RESIDENT fixed_length_combinations;

    DROP TABLE fixed_length_combinations;

NEXT vOuterLoop

Community Browser