Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
karan_kn
Creator II
Creator II

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

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

Category.JPG

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

1 Reply
sunny_talwar

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;