There are many functions and techniques within Qlik Sense that one might forget to recall when a challenge is presented. These functions can always use practical examples of implementation and use case scenarios. Let’s look at the FROM_FIELD function in Qlik Sense.
Load * FROM_FIELD
Load * FROM_FIELD is a useful script function in Qlik Sense that allows you to parse the contents of a single column into many columns or rows. Here are some practical examples:
- Address: Client Addresses are captured within a single column on the source system.
- Financial: Chart of Accounts – Allows the jagged hierarchy of the chart of accounts to be read from a single field and transposed to rows or columns.
- Sales: Bill of Material – Lines on an Invoice within the Inventory Management Systems a Bill of Material can have a jagged hierarchy stored within a single column within the source system. This will then need separated into columns for reporting purposes.
- Website: Data captured within a single column and needs presented in multiple columns.
This script example provides the Address parsed out into columns:
Address: Load * Inline [ Customer_ID, CustomerData 1101, 1101|Catering Service|25648 Fisherman Drive|Bellevue|NE|68005|United States 1102, 1102|Wedding Venue|PO Box 354 RW|Portsmouth|NH|03801|United States ]; Columns: Load * FROM_FIELD (Address, CustomerData) (txt, utf8, no labels, delimiter is '|', msq);
This example script provides the Web Data parsed out into rows.
Websites: Load * Inline [ Website_ID, URLs 2546478SW$@265, www.cnn.com|www.foxnews.com|www.usatoday.com|www.nytimes.com 152478ER%^&154, www.google.com|www.snapchat.com|www.facebook.com|www.twitter.com ]; Rows: Load * FROM_FIELD (Websites, URLs) (txt, utf8, no labels, delimiter is '|', filters(Transpose()));
Alternatively, this can then be used as a filter within the application, not just for output in a chart or graph.
Qlik Sense offers flexibility and creativity to overcome the challenges of data and how best to present it. This example of the FROM_FIELD function will hopefully help spark a creative solution to your next challenge.
Keep Reading: Cumulative Sum Expression Writing In Qlik Sense