Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thanks Nicole,
but when I click on Table Viewer, try to preview, there is no data on created tables except Synthetic table. Any suggestion?
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).
The question is, why do you need those brand specific tables at all?
How are you using these extra tables?
regards
Marco
Actually I'm using it as Mapping load so synthetic tables is not an issue.
Thanks.