Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vijetasharma
Contributor III

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: lookup scenario

Can you close the thread by selection correct answer?

12 Replies
Not applicable

Re: lookup scenario

Try this in the load script...

t1:

LOAD * Inline [id1,id2,value

A,1,100

B,2,120

C,3,140

];

Outer Join

LOAD * Inline [id1,pg1

A,pqr

B,

D,xyz

E,123

];

Outer Join

LOAD * Inline [id2,pg2

1,abc

2,mno

3,tuv

4,xyz

];

Result:

LOAD

id1,

id2,

pg1,

pg2,

Sum(value) as value1

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

Drop Table t1;

MVP
MVP

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: lookup scenario

as Jonathan says you don't need to join anything

t1:                      

load * inline [

id1,id2,value

A,1,100

B,2,120

C,3,140

];

t2:

load * inline [

id1,pg1

A,pqr

B,

D,xyz

E,123

];

t3:

load * inline [

id2,pg2

1,abc

2,mno

3,tuv

4,xyz

];

but the RESULT is slightly different from your

id1id2pg1sum(value)
A1pqr100
B2 120
MVP
MVP

Re: lookup scenario

TEMP:

Mapping Load * Inline

[

  id2, pg2

  1, abc

  2, mno

  3, tuv

  4, xyz

];

T1:  

Load * Inline

[                  

  id1, id2, value

  A, 1, 100

  B, 2, 120

  C, 3, 140

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

Left Join

Load * Inline

[

  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;

vijetasharma
Contributor III

Re: lookup scenario

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

MVP
MVP

Re: lookup scenario

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

vijetasharma
Contributor III

Re: lookup scenario

Thanks,It's working fine

MVP
MVP

Re: lookup scenario

Can you close the thread by selection correct answer?

vijetasharma
Contributor III

Re: lookup scenario

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

Community Browser