Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Dept:
Mapping Load * Inline [
DeptID, Location
10, 200
10, 100
];
Emps:
Load *,
ApplyMap('Dept',DeptID) as Location;
Load * Inline [
EmployeeID, DeptID
1213, 10
];
When I reload I am getting a single record in Emps table with the first match ie 200, is there a way to get two records in the EMps table
as
1213, 10, 200
1213, 10, 100
I can get it with joins but the situation is different with real data.
Mapping Load
DeptID,
Concat(Location,'|') as List
Inline [
DeptID, Location
10, 200
10, 100
] Group by DeptID ;
Emps:
Load
EmployeeID,
subfield(ApplyMap('Dept',DeptID),'|') as Location
Inline [
EmployeeID, DeptID
1213, 10
];
Well, yes you could concatenate the values in the mapping table and then use the subfield function to create separate records again for each value in the list. But you should just use a join instead.
Perhaps This
Dept:
LOAD * INLINE [
DeptID, Location
10, 200
10, 100
];
Join
Load * Inline [
EmployeeID, DeptID
1213, 10
];
Note: Real data, What you are facing?
Can you give the script ??
Try like this
Dept:
Load * Inline [
DeptID, Location
10, 200
10, 100
];
T:
Mapping LOAD DeptID,
Concat(Location,',') as L
resident
Dept
group by
DeptID;
Emps:
Load *,
ApplyMap('T',DeptID) as Location;
Load * Inline [
EmployeeID, DeptID
1213, 10
];
I agree, one way could be to do a left join. But what exactly are you trying to do?
Mapping Load
DeptID,
Concat(Location,'|') as List
Inline [
DeptID, Location
10, 200
10, 100
] Group by DeptID ;
Emps:
Load
EmployeeID,
subfield(ApplyMap('Dept',DeptID),'|') as Location
Inline [
EmployeeID, DeptID
1213, 10
];
try this
final:
Load * Inline [
EmployeeID, DeptID
1213, 10
];
left join (final)
LOAD * INLINE [
DeptID, Location
10, 200
10, 100
];
Joining is the ultimate but we are insisted not to do a join till last try, as they are all transaction tables with huge data
With large data, it makes sense to not use joins. Use what Gysbert has proposed then.