Assigning Next Field Value to Previous Field if the Previous field is null

[ManimekalaiS]

INPUT:

Load * Inline

[Name,L1,L2,L3,L4,L5

AAA,BB, ,CC,DD,EE

BBB,DD,,,GG,MM

];

TRANSFORM1:

CrossTable(Hierarchy,Role)

Load * Resident INPUT;

Drop Table INPUT;

TRANSFORM2:

NoConcatenate

Load

Name,

Hierarchy,

Role,

If(Name=Peek(Name),Peek('Number')+1,1) as Number

Resident TRANSFORM1 Where Len(Role)>0 Order by Name,Hierarchy Asc;

Drop Table TRANSFORM1;

OUTPUT:

NoConcatenate

Load Name,'L'&Number as New_Hierarchy,Role,Name&'L'&Number as Name_Hierarchy_Key Resident TRANSFORM2

Order by Name,Hierarchy Asc;

Drop Table TRANSFORM2;

Concatenate(OUTPUT)

Load Name, 'L'&Number1 as New_Hierarchy, null() as Role where not Exists(Name_Hierarchy_Key,Name&'L'&Number1);

Load Name,1+IterNo()-1 as Number1

While 1+IterNo()-1 <=5;

Load Name, Sum(0) as Dummy

Resident OUTPUT group by Name;

Drop Field Name_Hierarchy_Key;

Input and Output File Screenshot: