Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement.
I have the data like below.
Here same trade keys is in three Different Types i.e, Internal, External and Global.
Requirement is when any id present in Internal, External and Global Types need to keep that id in Internal Type
If there Is no Internal then need to keep in External else in Global.
Please help me on this. Thanks in advance
Input :
Key Type Identfier
1578693 Internal afv
1578693 External sf_erkil
1578693 Global huk_lop
1578983 Internal afv
1578983 External sg_kilo
1578988 Global hjkl
1578988 External siklo
15789698 Global olp
Expected Output:
Key Type Identfier
1578693 Internal afv
1578983 Internal afv
1578988 External siklo
15789698 Global olp
Maybe like
Output:
LOAD *, Key as Keycheck RESIDENT Input
WHERE Type = 'Internal';
CONCATENATE
LOAD *, Key as Keycheck RESIDENT Input
WHERE Type = 'External' and not exists(Keycheck,Key);
CONCATENATE
LOAD * RESIDENT Input
WHERE Type = 'Global' and not exists(Keycheck,Key);
DROP FIELD Keycheck;
Another option would be to assign type a dual value with a numeric sort order value, then LOAD your input table like this:
NOCONCATENATE
LOAD
Key,
MinString(Type) as Type,
FirstSortedValue(Identifier, Type) as Identifier
RESIDENT Input
GROUP BY Key;
Hi,
this will not be if logic, rather data load logic.
Input:
LOAD
Key,
Type,
Identifier,
If(Type='Internal', Key) as _internalKeys,
If(Type='External', Key) as _externalKeys
inline [
Key, Type, Identfier
1578693, Internal, afv
1578693, External, sf_erkil
1578693, Global, huk_lop
1578983, Internal, afv
1578983, External, sg_kilo
1578988, Global, hjkl
1578988, External, siklo
15789698, Global, olp
];
Output:
LOAD distinct
Key,
Key as _processedKey,
Type,
Type,
Identifier
Resident Input
Where
Not(IsNull(_internalKeys))
;
Concatenate(Output)
LOAD distinct
Key,
Key as _processedKey,
Type,
Type,
Identifier
Resident Input
Where
Not(IsNull(_externalKeys))
and
Not(Exists(_processedKey, _externalKeys))
;
Concatenate(Output)
LOAD distinct
Key,
Key as _processedKey,
Type,
Type,
Identifier
Resident Input
Where
Not(Exists(_processedKey, Key))
;
Drop Field _processedKey;
Hope this helps.
Juraj