qlikview load- setting a field name by condition

[#1]

Hi !!

i have 2 tables in my DB:

a:

a.id	a.name	b.id	b.name
1	a1	1	b1
2	a2	2	b2
3	a3	3	b3

c:

c.id	c.type	a.id	b.id	c.name
1	a	1	3	a1
2	a	2	2	a2
3	b	2	1	b1

i want to fill the column c.name in this method:

if c.type=a then fill c.name with a.name

if c.type=b then fill c.name with b.name

its like doing a join between two tables, sometimes on a.id and sometimes on b.id

Thanks !!

Mor

[swuehl · Accepted solution]

I think there are multiple approaches.

You can join your first table to the second in two steps, first join a.id / a.name then b.id / b.name,

then filter your resulting table using c.type and remove the fields not necessary.

Or use lookup() function:

First load your first table.

In the second table load, use the lookup function:

LOAD c.id,

          c.type,

          a.id,

          b.id,

          if(c.type = 'a', lookup('a.name','a.id',a.id,'FirstTableName'),

                    lookup('b.name','b.id',b.id,'FirstTableName') )

                              as c.name

FROM SecondTable;

drop FirstTableName;

[#3]

the lookup function workes !!

thanks man !