topic Re: Search Value in comma delimited Field in QlikView

Search Value in comma delimited Field

Tue, 23 Dec 2014 04:37:46 GMT

Hi all,

I need help to search value in comma delimited filed. Can somebody help me. if you already have a sample qvw is also appreciated.

Eg. Filter power outages which impacted selected region

Outage date outage id outage regions ( simple table)

01/01/2013 1 EAST, NORTH

02/01/2013 2 SOUTH,WEST

03/02/2013 3 EAST

List of unique regions (List box for filter) ,

EAST

NORTH

WEST

SOUTH

Thanks

Chakri

Re: Search Value in comma delimited Field

tresB — Tue, 23 Dec 2014 04:51:20 GMT

Use subfield() that creates separate records for all delimited values of a field. Try like:

Load

[Outage Date],

[Outage Id],

SubField([Outage Region], ',') as [Outage Region]

From <>;

Re: Search Value in comma delimited Field

Tue, 23 Dec 2014 04:56:30 GMT

Thanks

This could be one way but i do not want display multiple records in detail list in case i search for two or more regions "NORTH,WEST" in search box.

Re: Search Value in comma delimited Field

Anonymous — Tue, 23 Dec 2014 06:19:53 GMT

You can use Wildcard search

Re: Search Value in comma delimited Field

Tue, 23 Dec 2014 06:33:15 GMT

could you please give me example or sample

I expect user select one or many by clicking in List box.

Re: Search Value in comma delimited Field

Anonymous — Tue, 23 Dec 2014 07:06:17 GMT

by for list search filter selecting the Spl column can reach the requirement

DATA;

LOAD [Outage date],

[outage id],

[outage regions],

SubField([outage regions],',')As Spl

FROM

[..\..\Downloads\Prod.xlsx]

(ooxml, embedded labels, table is Sheet2);

Re: Search Value in comma delimited Field

Tue, 23 Dec 2014 07:35:48 GMT

Thanks . Technically it works. I tested it. I am yet to join with look table that have unique list of regions.

but will it not increase load on Server?

I checked following forum that it will increase number of rows while loading though it helps to achieve desired functionality

The Secret Life of SubField

Re: Search Value in comma delimited Field

Anonymous — Tue, 23 Dec 2014 09:38:13 GMT

i read the given forum

If more than one table having reign means you can load separate table using resident load and concatenate function

if we use this only two column value table repeat the date this may reduce the performance compare to original fact table

Re: Search Value in comma delimited Field

Tue, 23 Dec 2014 09:46:50 GMT

Thanks

Still i want load only single record per each row in source data. In real scenario i have 6 or 7 comma delimited fields where i will search using different filters.

I am searching in another forum and tried concept of using variables . But it needs input box and button for action. I would like have the same functionality using List box will unique list of values

Comma separated searches | Qlik Community

Re: Search Value in comma delimited Field

jagannalla — Tue, 23 Dec 2014 10:28:08 GMT

Hope this helps you

Table1:

LOAD

[Outage Date],

[Outage Id],

[Outage Region]

From <>;

Table2:

Load Distinct

[Outage Region],

SubField([Outage Region], ',') as [Spl_Outage Region]

Resident Table1;