Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'll show what I need to achieve on example:
Lets have two tables
Table1:
LOAD * INLINE [
Table_Code, Table_Carrier
'AA', 'E3'
'BB', 'R7'
'CC', 'F5'
];
Type:
LOAD * INLINE [
Type_Code, Type_Carrier, Type_Data
'AA', 'E3', 'AA E3'
'AA', 'R7', 'AA R7'
'AA', '', 'general AA'
'BB', 'E3', 'BB E3'
'BB', '', 'general BB'
];
I would like to list all rows from Table1 and join Type_Data from Type table to them. Rules are:
I have found this solution
J:
LOAD
Table_Carrier as Carr,
Table_Code as code
Resident
Table1;
left join
LOAD
Type_Carrier as Carr,
Type_Code as code,
Type_Data
Resident
Type;
left join
LOAD
Type_Code as code,
Type_Data as Type_Data_G
Resident
Type
WHERE
IsNull(Type_Carrier) or Type_Carrier = '';
F:
NoConcatenate LOAD
Carr,
code,
if(not IsNull(Type_Data), Type_Data, Type_Data_G) as Type_Data
Resident
J;
But it doesn't seems 'elegant' to me. Isn't there any better way how to get to the right result? Using VB is probably a way but the user is accessing project via AJAX thin client and I'm not sure if the VB will work in this case.
Regards,
Jindra
TypeMap:
mapping
LOAD Type_Code&Type_Carrier as Key, Type_Data INLINE [
Type_Code, Type_Carrier, Type_Data
'AA', 'E3', 'AA E3'
'AA', 'R7', 'AA R7'
'AA', '', 'general AA'
'BB', 'E3', 'BB E3'
'BB', '', 'general BB'
];
Table1:
LOAD *, ApplyMap('TypeMap',Table_Code&Table_Carrier,ApplyMap('TypeMap',Table_Code,'No Value')) as Type_Data INLINE [
Table_Code, Table_Carrier
'AA', 'E3'
'BB', 'R7'
'CC', 'F5'
];
TypeMap:
mapping
LOAD Type_Code&Type_Carrier as Key, Type_Data INLINE [
Type_Code, Type_Carrier, Type_Data
'AA', 'E3', 'AA E3'
'AA', 'R7', 'AA R7'
'AA', '', 'general AA'
'BB', 'E3', 'BB E3'
'BB', '', 'general BB'
];
Table1:
LOAD *, ApplyMap('TypeMap',Table_Code&Table_Carrier,ApplyMap('TypeMap',Table_Code,'No Value')) as Type_Data INLINE [
Table_Code, Table_Carrier
'AA', 'E3'
'BB', 'R7'
'CC', 'F5'
];
Thank you, that works perfectly