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

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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
];

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

sunny_talwar — Fri, 01 Nov 2019 16:31:41 GMT

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;

topic Re: How to extract specific data from the field and aligned the field in QlikView

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

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