Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vijetas42
Specialist
Specialist

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
MK_QSL
MVP
MVP

Can you close the thread by selection correct answer?

View solution in original post

12 Replies
Not applicable

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;

jonathandienst
Partner - Champion III
Partner - Champion III

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
maxgro
MVP
MVP

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
MK_QSL
MVP
MVP

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;

vijetas42
Specialist
Specialist
Author

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

MK_QSL
MVP
MVP

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

vijetas42
Specialist
Specialist
Author

Thanks,It's working fine

MK_QSL
MVP
MVP

Can you close the thread by selection correct answer?

vijetas42
Specialist
Specialist
Author

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