Combining tables, Fieldnames and Values

[fred_s]

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

[#2]

plz tell me which field is common in both table

[#3]

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 .

[fred_s]

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

[biester]

Perhaps attached example would be an option.

Rgds,
Joachim

[fred_s]

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

[johnw · Accepted solution]

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]
;

[biester]

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

[fred_s]

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!

[johnw]

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.