Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Can you close the thread by selection correct answer?
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;
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.
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
id1 | id2 | pg1 | sum(value) |
A | 1 | pqr | 100 |
B | 2 | 120 |
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;
sorry,but i am having pg2 instead of pg1 in table t3
Check my solution. I have tested and it's working as per your requirement.
Thanks,It's working fine
Can you close the thread by selection correct answer?
Hi Manish your script is not working when i am doing same thing with excel/qvd files?