Making Dynamic Mapping Tables

[#1]

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.

[Nicole-Smith · Accepted solution]

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

[#3]

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]

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]

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

How are you using these extra tables?

regards

Marco

[#6]

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

Thanks.