Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Making Dynamic Mapping Tables

Hello all,

Any input will be appreciated

I have a table where there are 3 column:

RANK:

ID          Brand     Rank

1             a            1

1             b            3

1             c            2

2             a            4

2             b            7

3             a            1

3             b            5

I was using this code:

a_Ranking:

Load *,

Resident Rank

where Brand = 'a';

b_Rating:

Load *,

Resident Rank

where Brand = 'b';

c_Rating:

Load *,

Resident Rank

where Brand = 'c';

Therefore I was hardcoding and filtering the tables according to brand. Is there any way that I can make it dynamic? Let's say in the future they add new brand, the table will be generated automatically.

Thanks.

1 Solution

Accepted Solutions
Nicole-Smith
Not applicable

Re: Making Dynamic Mapping Tables

Something like this should accomplish it:

Rank:

LOAD * INLINE [

    ID,Brand,Rank  

    1,a,1  

    1,b,3  

    1,c,2  

    2,a,4  

    2,b,7  

    3,a,1  

    3,b,5

];

Brand:

LOAD concat(distinct chr(39) & Brand & chr(39), ', ') as BrandList

RESIDENT Rank;

LET vBrand = PEEK('BrandList', 0, 'Brand');

DROP TABLE Brand;

FOR EACH a IN $(vBrand)

    $(a)_Ranking:

    NOCONCATENATE LOAD *

    RESIDENT Rank

    WHERE Brand = '$(a)';

NEXT a

5 Replies
Nicole-Smith
Not applicable

Re: Making Dynamic Mapping Tables

Something like this should accomplish it:

Rank:

LOAD * INLINE [

    ID,Brand,Rank  

    1,a,1  

    1,b,3  

    1,c,2  

    2,a,4  

    2,b,7  

    3,a,1  

    3,b,5

];

Brand:

LOAD concat(distinct chr(39) & Brand & chr(39), ', ') as BrandList

RESIDENT Rank;

LET vBrand = PEEK('BrandList', 0, 'Brand');

DROP TABLE Brand;

FOR EACH a IN $(vBrand)

    $(a)_Ranking:

    NOCONCATENATE LOAD *

    RESIDENT Rank

    WHERE Brand = '$(a)';

NEXT a

Not applicable

Re: Making Dynamic Mapping Tables

Thanks Nicole,

but when I click on Table Viewer, try to preview, there is no data on created tables except Synthetic table. Any suggestion?

Nicole-Smith
Not applicable

Re: Making Dynamic Mapping Tables

It's because of the synthetic key.  You'll probably want to get rid of the synthetic key, but I don't know enough about your data model in order to tell you how to do so.  To get rid of it, you'll either need to rename fields or create a concatenated key (http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys).

MarcoWedel
Not applicable

Re: Making Dynamic Mapping Tables

The question is, why do you need those brand specific tables at all?

How are you using these extra tables?

regards

Marco

Not applicable

Re: Making Dynamic Mapping Tables

Actually I'm using it as Mapping load so synthetic tables is not an issue.

Thanks.