Help on if logic

[mahitham]

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

[swuehl]

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;

[swuehl]

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;

[juraj_misina]

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