topic AW:Combining tables, Fieldnames and Values in QlikView

Combining tables, Fieldnames and Values

fred_s — Tue, 01 Jun 2010 10:29:14 GMT

I have two tables, Category and Rel.

Combining tables, Fieldnames and Values

Tue, 01 Jun 2010 13:30:45 GMT

plz tell me which field is common in both table

Combining tables, Fieldnames and Values

Tue, 01 Jun 2010 13:40:44 GMT

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 .

Combining tables, Fieldnames and Values

fred_s — Tue, 01 Jun 2010 14:16:52 GMT

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

AW:Combining tables, Fieldnames and Values

biester — Tue, 01 Jun 2010 14:49:13 GMT

Perhaps attached example would be an option.

Rgds,
Joachim

Combining tables, Fieldnames and Values

fred_s — Tue, 01 Jun 2010 16:08:18 GMT

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

Combining tables, Fieldnames and Values

johnw — Tue, 01 Jun 2010 23:09:56 GMT

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

AW:Re: Combining tables, Fieldnames and Values

biester — Wed, 02 Jun 2010 06:42:52 GMT

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;

next;

Rgds,
Joachim

Combining tables, Fieldnames and Values

fred_s — Wed, 02 Jun 2010 09:47:08 GMT

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!

Combining tables, Fieldnames and Values

johnw — Wed, 02 Jun 2010 18:05:27 GMT

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.

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

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

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