Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Help with Filters

I have a field that has multiple regions. Here is an example:

Column: Region Impacted

Row 1: APAC EAME LATAM NA

Row 2: APAC

Row 3: LATAM EAME

Row 4: NA

Row 5: EAME NA

I want to filter on a region and have all records that have APAC in Region Impacted show. Can someone help?

1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

I can see how I have confused you now.  The first part is as per your original load, just with the label attached.  The second part uses that label.  Try this code:

LIB CONNECT TO 'Smartsheet Connector';

RegionGenData:

LOAD "Primary",

    "Primary ID",

    Created,

    Status,

    Stage,

    "Region Generating";

SQL SELECT "Primary",

    "Primary ID",

    Created,

    Status,

    Stage,

    "Region Generating"

FROM "Region Data__8841999244650372r_(Production and Supply/Qlik Sense)";

Regions:

Load

"Primary ID",

SubField([Region Generating], ' ') as RegionsG

Resident RegionGenData;

This assumes that the Primary ID is a unique ID.

Let me know how you get on.

Cheers,

Steve

View solution in original post

10 Replies
sunny_talwar

Try breaking the space separated Region into multiple rows using SubField() function

SubField(Region, ' ') as Region

Check here

The Secret Life of SubField

Anonymous
Not applicable
Author

Here is an example of the dataset. I want to filter on each region and get the records that have that region in "Region Generating". So if I select LATAM, I want all records that have LATAM. If I select APAC, I want all records that have APAC.

IDCreatedStatusStageRegion Generating
4783/22/2018 8:36ActiveEvaluationNA EAME APAC
4773/21/2018 13:22ActiveEvaluationAPAC
4763/21/2018 11:42ActiveEvaluationNA APAC
4753/21/2018 10:52ActiveEvaluationEAME
4733/19/2018 14:17ActiveEvaluationEAME APAC
4723/16/2018 16:06ActiveEvaluationNA APAC LATAM
4713/16/2018 13:37ActiveEvaluationLATAM
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Michelle,

You want to load the table as it is, and give it a meaningful name, e.g.

MainData:

LOAD
    ID,

    Created,

    Status,

    Stage,

    [Region Generating] as [Region List]

FROM [... your data source here ...]

;

Once you have that you want to link each ID to each of the individual regions.  You can do this by doing a resident load (i.e. loading from a table already in memory) and a SubField (as stalwar1‌) suggests.  The code is like this:

Regions:

LOAD

     ID,
     SubField([Region List], ' ') as Region

RESIDENT MainData

;


What you will find is that you have two tables, associated by ID, the first with one row per source row and the other with one row per region.


This should give you what you require.


Steve

Anonymous
Not applicable
Author

One last thing (hopefully)...I am now getting "no qualified path" error message.

Anonymous
Not applicable
Author

Here is what I have so far.

[21-12-17 Extract__6846337053419396s_(Production and Supply/Qlik Sense)]:

RegionGenData:

LOAD

  "ID",

  [Region Generating] as RegionGenList

FROM "21-12-17 Extract__6846337053419396s_(Production and Supply/Qlik Sense)";

RegionsG:

Load

"ID",

SubField(RegionGenList, ' ') as RegionsG

Resident RegionGenData;

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

What line is it failing on?

Try removing this line:

[21-12-17 Extract__6846337053419396s_(Production and Supply/Qlik Sense)]:


You currently have a duplicate table name which could be confusing things.


Is [Region Generating]  actually the name of a field in your data source?


The items in the FROM line, was that created by the Data Manager?  I would expect whatever appears in there to start with Lib://


Steve

Anonymous
Not applicable
Author

I am importing data from Smartsheet. And [Region Generating] is the name of the field in the source data.

LIB CONNECT TO 'Smartsheet Connector';

LOAD "Primary",

    "Primary ID",

    Created,

    Status,

    Stage,

    "Region Generating";

SQL SELECT "Primary",

    "Primary ID",

    Created,

    Status,

    Stage,

    "Region Generating"

FROM "Region Data__8841999244650372r_(Production and Supply/Qlik Sense)";

RegionGenData:

LOAD

"Primary ID",

Created,

Status,

Stage,

[Region Generating] as RegionGenList

FROM "Region Data__8841999244650372r_(Production and Supply/Qlik Sense)";

Regions:

Load

"Primary ID",

Created,

Status,

Stage,

SubField(RegionGenList, ' ') as RegionsG

FROM Resident RegionGenData;

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

I can see how I have confused you now.  The first part is as per your original load, just with the label attached.  The second part uses that label.  Try this code:

LIB CONNECT TO 'Smartsheet Connector';

RegionGenData:

LOAD "Primary",

    "Primary ID",

    Created,

    Status,

    Stage,

    "Region Generating";

SQL SELECT "Primary",

    "Primary ID",

    Created,

    Status,

    Stage,

    "Region Generating"

FROM "Region Data__8841999244650372r_(Production and Supply/Qlik Sense)";

Regions:

Load

"Primary ID",

SubField([Region Generating], ' ') as RegionsG

Resident RegionGenData;

This assumes that the Primary ID is a unique ID.

Let me know how you get on.

Cheers,

Steve

Anonymous
Not applicable
Author

Worked like a charm! Thank you for sticking with me. I can only imagine that was a bit frustrating.