Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables, Category and Rel.
CATEGORY
Nr | Category | Code | Description |
1 | Sport | A | All |
1 | Sport | T | Tennis |
4 | Emailing | 0 | No |
4 | Emailing | 1 | Yes |
4 | Emailing | 2 | Important only |
5 | Accountmanager | T | Torvald |
5 | Accountmanager | P | Peter |
5 | Accountmanager | A | Allen |
Rel
Debname | Cat001 | Cat002 | Cat003 | Cat004 | Cat005 | Cat006 | Cat007 | Cat008 | Cat009 |
Clinton | A | 0 | A | ||||||
Doe J. | A | 2 | T | ||||||
Bush | 1 | A | |||||||
Kennedy | T | 1 | P |
What I would like to achieve is a new table, based on REL but Fieldnames and Values based on Category, see example below.
Debname | Sport | Cat002 | Cat003 | Emailing | Accountmanager | Cat006 | Cat007 | Cat008 | Cat009 |
Clinton | All | No | Allen | ||||||
Doe J. | All | Important only | Torvald | ||||||
Bush | Yes | Allen | |||||||
Kennedy | Tennis | Yes | Peter |
Thanks in advance
Well, here's my attempt. First I create mapping tables for the fields and the values. Then I do a crosstable load. This turns my column names into values that I can apply the mapping to. I also apply mapping to the values. Finally, I do a generic load to turn the values back into field names. The problem is that the generic load appears to create one table per field, which isn't particularly good. Might be a way to fix it. I don't know how.
[Map Fields]:
MAPPING LOAD
Nr
,maxstring(Category) as Category
RESIDENT [Category]
GROUP BY Nr
;
[Map Values]:
MAPPING LOAD
Nr&':'&Code as Key
,Description
RESIDENT [Category]
;
New:
CROSSTABLE (Field,Value)
LOAD *
RESIDENT [REL]
;
New2:
GENERIC LOAD
Debname
,applymap('Map Fields',right(Field,1),Field) as Field
,applymap('Map Values',right(Field,1)&':'&Value,Value) as Value
RESIDENT New
;
DROP TABLES
[Category]
,[REL]
,[New]
;
plz tell me which field is common in both table
Hiii Fred ,
There is no data modelling in your both tabless. u should have atleast 1 field common b/w thm.
try to add Code in you Rel table. It will work .
I'm afraid it's a bit more complex
Category.nr 1 (value) = Rel.Cat001 (Fieldname)
Category.nr 4 (value) = Rel.Cat004 (Fieldname)
* *
What I need to achieve, for a start, is to
- rename Field rel.Cat001 to the (value of Category.Category (where nr = 1))
- rename Field rel.Cat004 to the (value of Category.Category (where nr = 4))
- rename Field rel.Cat009 to the (value of Category.Category (where nr = 9))
etc
I need to rename Fieldname "Cat001" to the new name "Sport"
Fieldname Cat004 to Emailing
Fieldname Cat005 to Accountmanager
Perhaps attached example would be an option.
Rgds,
Joachim
Hi Joachim,
Nice alternative and thanks for thinking along.
Actually my REL table is a lot bigger than in my example (more fields) and with this Solution we would get a REL table nine times as big.
My question is just an example.
I have the same problem or situation with more tables.
The customer has several Free fields and they can decide themselves how they call the Fields and what values to use.
The most important part of my question is;
Is it possible to use some loop function to rename fieldnames with predictable Fieldvalues?
Thanks Guys
Fred
Well, here's my attempt. First I create mapping tables for the fields and the values. Then I do a crosstable load. This turns my column names into values that I can apply the mapping to. I also apply mapping to the values. Finally, I do a generic load to turn the values back into field names. The problem is that the generic load appears to create one table per field, which isn't particularly good. Might be a way to fix it. I don't know how.
[Map Fields]:
MAPPING LOAD
Nr
,maxstring(Category) as Category
RESIDENT [Category]
GROUP BY Nr
;
[Map Values]:
MAPPING LOAD
Nr&':'&Code as Key
,Description
RESIDENT [Category]
;
New:
CROSSTABLE (Field,Value)
LOAD *
RESIDENT [REL]
;
New2:
GENERIC LOAD
Debname
,applymap('Map Fields',right(Field,1),Field) as Field
,applymap('Map Values',right(Field,1)&':'&Value,Value) as Value
RESIDENT New
;
DROP TABLES
[Category]
,[REL]
,[New]
;
For dynamically renaming fields you could use a similar loop (it's important to know that lookup delivers only the first value it finds - the Category sample table is not normalized, there are redundancies of course and theoretically it would be possibe that e. g. the first row has "1,Emailing....", and the second row has "1, Sport"). But generally this thing works.
let REL_FIELD_COUNT = NoOfFields('REL');
let CAT_FLAG = 'Cat';
for counter = 1 to $(REL_FIELD_COUNT)
let fn = FieldName(counter,'REL');
if (index('$(fn)','$(CAT_FLAG)') = 1 ) then
let key = num(replace('$(fn)','$(CAT_FLAG)',''));
let Keyval = lookup('Category','Nr',key,'Category');
if (len(Keyval) > 0) then
rename field $(fn) to $(Keyval);
end if;
end if;
next;
Rgds,
Joachim
Joachim,
Your solution didn't fix this case, but sure is a handy way for similar problems I still have.
Thanks!
John,
You did it again!
Maybe you're getting a bit tired of all these happy people, but I can't help it.
I fixed the Generic Load problem (Table per Field) with the Rob Wunderlich solution:
http://community.qlik.com/blogs/qlikviewblogs/archive/2010/05/21/use-cases-for-generic-load.aspx
Next thing I did was to add an underscore to the fieldnames, so I can recognize them as Free Fields.
I'd like to create a Generic solution, for example with a Multiple Select Box.
For this I guess it's handy to have 'something' to recognize the Free Fields.
Thanks again!
Ah, cool. I've now applied Rob's solution to my example. Seems to work great. Not sure if it's worth the overhead, but better that the example at least give people the option of combining everything back into one table.