Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kostas_Lag
Partner - Contributor III
Partner - Contributor III

Create unique combinations and more

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:

DimensionsRank
Country1
Category2
Channel3
Vendor4

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:

  

  

NoOfDimsDim1Dim2Dim3Dim4RankDim
1CountryCountry
1CategoryCategory
1ChannelChannel
1VendorVendor
2CountryCategoryCategory
2CountryChannelChannel
2CountryVendorVendor
2CategoryCountryCategory
2CategoryChannelChannel
2CategoryVendor

Vendor

2ChannelCountryChannel
2ChannelCategoryChannel
2ChannelVendorVendor
2VendorCountryVendor
2VendorCategoryVendor
2VendorChannelVendor
3CountryCategoryChannelChannel
3CountryCategoryVendorVendor
3CountryChannelVendorVendor
4CountryCategoryChannelVendorVendor
4CountryChannelVendorCategoryVendor
4CategoryChannelVendorCountryVendor
etcetcetcetcetcetc

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

Thanks in advance,

Kostas

1 Solution

Accepted Solutions
mikevwang
Contributor III
Contributor III

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;

View solution in original post

5 Replies
vitaliichupryna
Creator III
Creator III

Hi Kostas,

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

Thanks,

Vitalii

mikevwang
Contributor III
Contributor III

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;

Kostas_Lag
Partner - Contributor III
Partner - Contributor III
Author

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

mikevwang
Contributor III
Contributor III

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;

Kostas_Lag
Partner - Contributor III
Partner - Contributor III
Author

Hi Mike!

That was indeed perfect!

Thank you so much!

Best regards,

Kostas