How to extract specific data from the field and aligned the field

[karan_kn]

Extract the specific text from the field and aligned it in the correct field.

Classified the fields based on the defined field. Please refer the screenshot below

LOAD * INLINE [
ID, Field1
1, DolphinTigerHumming bird
2, ParrotPolar BearBlue Whale
3, Polar BearParrotDolphin
4, DolphinOwlTiger
5, OwlBlue WhaleTiger
6, Humming birdPolar BearBlue Whale
7, DolphinParrotPolar Bear
8, TigerParrotBlue Whale
9, Humming birdTigerDolphin
10, Polar BearOwlDolphin
];

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD If(not Match(Category, 'Animal', 'Bird', 'Fish'), Peek('Category'), Category) as Category,
	 If(not Match(Category, 'Animal', 'Bird', 'Fish'), Category) as Type;
LOAD * INLINE [
    Category 
    Animal
    Tiger
    Polar Bear
    Bird
    Parrot
    Owl
    Humming bird
    Fish
    Dolphin
    Blue Whale
];

MappingTable1:
Mapping
LOAD Type,
	 Type & ','
Resident Table
Where Len(Trim(Type)) > 0;

MappingTable2:
Mapping
LOAD Type,
	 Category
Resident Table
Where Len(Trim(Type)) > 0;

DROP Table Table;

Fact:
LOAD ID,
	 ApplyMap('MappingTable2', Type, 'N/A') as Category,
	 Type
Where Len(Trim(Type)) > 0;
LOAD *,
	 SubField(MapSubString('MappingTable1', Field1), ',') as Type;
LOAD * INLINE [
    ID, Field1
    1, DolphinTigerHumming bird
    2, ParrotPolar BearBlue Whale
    3, Polar BearParrotDolphin
    4, DolphinOwlTiger
    5, OwlBlue WhaleTiger
    6, Humming birdPolar BearBlue Whale
    7, DolphinParrotPolar Bear
    8, TigerParrotBlue Whale
    9, Humming birdTigerDolphin
    10, Polar BearOwlDolphin
];

FinalFact:
LOAD DISTINCT ID
Resident Fact;

FOR i = 1 to FieldValueCount('Category')

	LET vCategory = FieldValue('Category', $(i));
	
	Left Join (FinalFact)
	LOAD DISTINCT ID,
		 Type as [$(vCategory)]
	Resident Fact
	Where Category = '$(vCategory)';
	
NEXT

DROP Table Fact;