topic Re: Create unique combinations and more in QlikView

Create unique combinations and more

Kostas_Lag — Mon, 05 Nov 2018 20:19:29 GMT

Hello everyone,

I hope you could help me with something.

I 'd like to create a qvw which takes as an input a table like this:

Dimensions	Rank
Country	1
Category	2
Channel	3
Vendor	4

And the output would be a table with all possible unique combinations of Dimensions and for each combination(row), there should be a field with the dimension with the highest ranking of that specific combination.

For example:

NoOfDims	Dim1	Dim2	Dim3	Dim4	RankDim
1	Country				Country
1	Category				Category
1	Channel				Channel
1	Vendor				Vendor
2	Country	Category			Category
2	Country	Channel			Channel
2	Country	Vendor			Vendor
2	Category	Country			Category
2	Category	Channel			Channel
2	Category	Vendor			Vendor
2	Channel	Country			Channel
2	Channel	Category			Channel
2	Channel	Vendor			Vendor
2	Vendor	Country			Vendor
2	Vendor	Category			Vendor
2	Vendor	Channel			Vendor
3	Country	Category	Channel		Channel
3	Country	Category	Vendor		Vendor
3	Country	Channel	Vendor		Vendor
4	Country	Category	Channel	Vendor	Vendor
4	Country	Channel	Vendor	Category	Vendor
4	Category	Channel	Vendor	Country	Vendor
etc	etc	etc	etc	etc	etc

I know it might be a long shot but any help would be very much appreciated.

Thanks in advance,

Kostas

Re: Create unique combinations and more

vitaliichupryna — Tue, 06 Nov 2018 13:36:41 GMT

Hi Kostas,

Check this article: Complex Authorization - How to create authorization bridge

Thanks,

Vitalii

Re: Create unique combinations and more

mikevwang — Wed, 07 Nov 2018 00:45:55 GMT

I'm sure there are more elegant solutions, but the following code would work:

Input:

LOAD * INLINE [

    Dimensions, Rank

    Country, 1

    Category, 2

    Channel, 3

    Vendor, 4

];

RankToDimMap:

Mapping LOAD Rank, Dimensions Resident Input;

RankOutput:

NoConcatenate

LOAD

    1 as NoOfDims,

    Rank as Dim1,

    Rank as RankDim

Resident Input;

FOR i = 2 to NoOfRows('Input')

    LET j = i-1;



    Temp.NextBlock:

    NoConcatenate

    LOAD Rank as Dim$(i) Resident Input;



    Inner Join(Temp.NextBlock)

    LOAD *, RankDim as OldRankDim Resident RankOutput

    Where NoOfDims = $(i) - 1;



    DROP Fields RankDim, NoOfDims

    From Temp.NextBlock;



    NextBlock:

    NoConcatenate

    LOAD *,

        $(i) as NoOfDims,

        RangeMax(OldRankDim,Dim$(i)) as RankDim

    Resident Temp.NextBlock

    Where Dim$(j)<>Dim$(i);



    DROP Fields OldRankDim From NextBlock;



    Concatenate(RankOutput)

    LOAD * Resident NextBlock;

    DROP Tables Temp.NextBlock,    NextBlock;

NEXT

//Replace Ranks with Dimensions

MAP RankDim USING RankToDimMap;

FOR i = 1 to NoOfRows('Input')

    MAP Dim$(i) USING RankToDimMap;

NEXT

Output:

NoConcatenate

LOAD * Resident RankOutput;

DROP Table RankOutput;

Re: Create unique combinations and more

Kostas_Lag — Wed, 07 Nov 2018 15:29:59 GMT

Hi Mike!

Thanks a lot for your reply and the effort you put on this.

That was almost perfect!

I noticed that for NoOfDims 3 and 4 some values are available in more that one Dims.

In order to get the desired output, I added that extra piece of code where everything is unfortunately hardcoded

NewOutput:

NoConcatenate

Load

Resident Output

where NoOfDims=1 or (Dim1<>Dim3 and Dim1<>Dim4 and Dim2<>Dim4);

DROP Table Output;

Any ideas if this can be done somehow dynamic?

Thanks again,

Kostas

Re: Create unique combinations and more

mikevwang — Thu, 08 Nov 2018 00:26:08 GMT

I see what you are trying to do .

Input:

LOAD * INLINE [

    Dimensions, Rank

    Country, 1

    Category, 2

    Channel, 3

    Vendor, 4

];

RankToDimMap:

Mapping LOAD Rank, Dimensions Resident Input;

RankOutput:

NoConcatenate

LOAD

    1 as NoOfDims,

    Rank as Dim1,

    Rank as RankDim

Resident Input;

FOR i = 2 to NoOfRows('Input')



    Temp.NextBlock:

    NoConcatenate

    LOAD Rank as Dim$(i) Resident Input;



    Inner Join(Temp.NextBlock)

    LOAD *, RankDim as OldRankDim Resident RankOutput

    Where NoOfDims = $(i) - 1;



    DROP Fields RankDim, NoOfDims

    From Temp.NextBlock;



    LET vDuplicationCheck='';

    FOR j = 1 to i-1

        IF Len(vDuplicationCheck)>0 THEN

            LET vDuplicationCheck = vDuplicationCheck & ' and ';

        ENDIF

        LET vDuplicationCheck = vDuplicationCheck & 'Dim$(j)<>Dim$(i)';

    NEXT



    NextBlock:

    NoConcatenate

    LOAD *,

        $(i) as NoOfDims,

        RangeMax(OldRankDim,Dim$(i)) as RankDim

    Resident Temp.NextBlock

    Where $(vDuplicationCheck);



    DROP Fields OldRankDim From NextBlock;



    Concatenate(RankOutput)

    LOAD * Resident NextBlock;

    DROP Tables Temp.NextBlock, NextBlock;

    LET vDuplicationCheck=;

NEXT

//Replace Ranks with Dimensions

MAP RankDim USING RankToDimMap;

FOR i = 1 to NoOfRows('Input')

    MAP Dim$(i) USING RankToDimMap;

NEXT

Output:

NoConcatenate

LOAD * Resident RankOutput;

DROP Table RankOutput;

Re: Create unique combinations and more

Kostas_Lag — Thu, 08 Nov 2018 10:13:25 GMT

Hi Mike!

That was indeed perfect!

Thank you so much!

Best regards,

Kostas