Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have these 2 tables and would like to obtain the result table.
Table1: | |
ID | Name |
1 | A |
2 | B |
3 | C |
Table2: | ||
ID | Company | Field1 |
1 | 1000 | X |
1 | 2000 | Y |
2 | 1000 | X |
2 | 2000 | Z |
3 | 2000 | Y |
Result: | |||
ID | Name | Comp.1000 | Comp.2000 |
1 | A | X | Y |
2 | B | X | Z |
3 | C | Y |
I hope anyone can help. Thanks
Try:
Result:
load ID, Name
from ...table1source...;
join
Load ID, Field1 as Comp1000
from ...table2source...
where Company = 1000;
join
Load ID, Field1 as Comp2000
from ...table2source...
where Company = 2000;
Try:
Result:
load ID, Name
from ...table1source...;
join
Load ID, Field1 as Comp1000
from ...table2source...
where Company = 1000;
join
Load ID, Field1 as Comp2000
from ...table2source...
where Company = 2000;
try to use generic load
*************************************
try this
Table1:
load * Inline [
ID, Name
1, A
2, B
3, C
];
join
Table2:
load * Inline [
ID, Company, Field1
1, 1000, X
1, 2000, Y
2, 1000, X
2, 2000, Z
3, 2000, Y
];
Generic LOAD ID,'Comp.' & Company ,Field1 Resident Table1;
Generic LOAD ID,'Comp.' &Company ,Field1 Resident Table1;
see the attached file
output like this
ID | Name | Comp1000 | Comp2000 |
1 | A | X | Y |
2 | B | X | Z |
3 | C | - | Y |