12 Replies Latest reply: Mar 7, 2014 4:29 AM by Manish Kachhia

# lookup scenario

Hi everyone,

I am having tables,

t1:

id1,id2,value

A,1,100

B,2,120

C,3,140

t2:

id1,pg1

A,pqr

B,

D,xyz

E,123

t3:

id2,pg2

1,abc

2,mno

3,tuv

4,xyz

after joining I want output like,

id1,id2,pg1,value

A,1,pqr,100

B,2,mno,120

how should I achieve it?

• ###### Re: lookup scenario

Try this in the load script...

t1:

A,1,100

B,2,120

C,3,140

];

Outer Join

A,pqr

B,

D,xyz

E,123

];

Outer Join

1,abc

2,mno

3,tuv

4,xyz

];

Result:

id1,

id2,

pg1,

pg2,

Sum(value) as value1

Resident t1 Group By id1, id2, pg1, pg2;

Drop Table t1;

• ###### Re: lookup scenario

Hi

Your data looks OK. Create a straight table with id1 and id2 as dimensions and pg1 and value as expressions. Turn off the totals on the expressions as they will have no meaning.

HTH

Jonathan

Edit: looking back, use sum(value) and leave totals on for that expression.

• ###### Re: lookup scenario

as Jonathan says you don't need to join anything

t1:

id1,id2,value

A,1,100

B,2,120

C,3,140

];

t2:

id1,pg1

A,pqr

B,

D,xyz

E,123

];

t3:

id2,pg2

1,abc

2,mno

3,tuv

4,xyz

];

but the RESULT is slightly different from your

 id1 id2 pg1 sum(value) A 1 pqr 100 B 2 120
• ###### Re: lookup scenario

sorry,but i am having pg2 instead of pg1 in table t3

• ###### Re: lookup scenario

TEMP:

[

id2, pg2

1, abc

2, mno

3, tuv

4, xyz

];

T1:

[

id1, id2, value

A, 1, 100

B, 2, 120

C, 3, 140

]Where Match(id1,'A','B');

Left Join

[

id1, pg1

A, pqr

B,

D, xyz

E, 123

];

NoConcatenate

FINAL:

Load id1, id2, value, IF(pg1='',ApplyMap('TEMP',id2),pg1) as pg1  Resident T1;

Drop Table T1;

• ###### Re: lookup scenario

Check my solution. I have tested and it's working as per your requirement.

• ###### Re: lookup scenario

Thanks,It's working fine

• ###### Re: lookup scenario

Hi Manish your script is not working when i am doing same thing with excel/qvd files?

• ###### Re: lookup scenario

let us have your script or sample qvd with limited data.

it should work;

• ###### Re: lookup scenario

I am doing this way,

t3:

pg1

FROM

D:\Qvds\t3.xlsx

(ooxml, embedded labels, table is Sheet1);

T1:

id2,

val

FROM

D:\Qvds\t1.xlsx

(ooxml, embedded labels, table is Sheet1)Where Match(id1,'A','B');

Inner join

t2:

pg1

FROM

D:\Qvds\t2.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

id1,id2,

IF(pg1='',ApplyMap('t3',id2),pg1) as pg1,val  Resident T1;

Drop Table T1;

• ###### Re: lookup scenario

Looks ok to me.. Can you attach your sample qvw?