Field that points to another field value in script

[igorgois_]

I have the following table with incident type and multiples subtype fields for each incident type

fact_temp:
load * inline [
id,incident_type,flag_subtype1,flag_subtype2
1,type1,abc,null
2,type2,null,cde
3,type2,null,xyz
4,type2,null,jki
5,type1,klm,null
];

I would like to create a new field called incident_subtype that gets the value of one of flag_subtypes depending on incident_type.

I can create a simple if like: if(incident_type='type1',flag_subtype1,flag_subtype2) as incident_subtype. But it turns out that I have a lot of subtypes.

One way that I was trying to do is to use a nested fieldvalue function, but I couldn't figure why is not working. Maybe the recno().

left join(fact_temp)
load * inline [
incident_type,pointer_field
type1,flag_subtype1
type2,flag_subtype2
];

fact:
load
*,
FieldValue(FieldValue('pointer_field',recno()),recno()) as incident_subtype
Resident fact_temp;

drop table fact_temp;

Thank in advance

[MayilVahanan]

Hi @igorgois_ 

Try like below

fact:
load *, RangeMaxString(Replace(flag_subtype1, 'null', null()), Replace(flag_subtype2, 'null', null())) as incident_subtype inline [
id,incident_type,flag_subtype1,flag_subtype2
1,type1,abc,null
2,type2,null,cde
3,type2,null,xyz
4,type2,null,jki
5,type1,klm,null
];

O/P:

If value is null in original table not string, then no need to use replace function.

[anthonyj · Accepted solution]

Hi @igorgois_ ,

If you have a lot "flag_subtype" another solution is to use crosstable  by the ID field and then sort out the nulls and join back to your fact table by an applymap.

fact_temp:
load * inline [
id,incident_type,flag_subtype1,flag_subtype2
1,type1,abc,null
2,type2,null,cde
3,type2,null,xyz
4,type2,null,jki
5,type1,klm,null
];

CrossTable:
crosstable(IncType, incident_subtype)
Load
id,
flag_subtype1,
flag_subtype2
Resident fact_temp;

SubtypesMap:
mapping
load distinct
id,
incident_subtype
Resident CrossTable
where incident_subtype <> 'null';

Fact:
load *,
ApplyMap('SubtypesMap',id,null()) as Incident_Subtype
Resident fact_temp;

drop table CrossTable, fact_temp;

Regards

Anthony

[igorgois_]

Hi, MayilVahanan 

Thanks for you answer but it turns out that when I have 99 subtype fields I will need 99 replaces functions.

[igorgois_]

Thanks Anthonyj!

It worked. Nice solution

#lifesaver