topic Making Dynamic Mapping Tables in QlikView

Making Dynamic Mapping Tables

Mon, 10 Mar 2014 15:53:39 GMT

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.

Re: Making Dynamic Mapping Tables

Nicole-Smith — Mon, 10 Mar 2014 16:03:04 GMT

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

Re: Making Dynamic Mapping Tables

Mon, 10 Mar 2014 20:53:19 GMT

Thanks Nicole,

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

Re: Making Dynamic Mapping Tables

Nicole-Smith — Tue, 11 Mar 2014 13:27:27 GMT

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).

Re: Making Dynamic Mapping Tables

MarcoWedel — Tue, 11 Mar 2014 23:19:56 GMT

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

How are you using these extra tables?

regards

Marco

Re: Making Dynamic Mapping Tables

Thu, 13 Mar 2014 15:52:19 GMT

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

Thanks.