Help with Filters

[Anonymous]

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?

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

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.

ID	Created	Status	Stage	Region Generating
478	3/22/2018 8:36	Active	Evaluation	NA EAME APAC
477	3/21/2018 13:22	Active	Evaluation	APAC
476	3/21/2018 11:42	Active	Evaluation	NA APAC
475	3/21/2018 10:52	Active	Evaluation	EAME
473	3/19/2018 14:17	Active	Evaluation	EAME APAC
472	3/16/2018 16:06	Active	Evaluation	NA APAC LATAM
471	3/16/2018 13:37	Active	Evaluation	LATAM

[stevedark]

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]

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

[Anonymous]

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]

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]

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 · Accepted solution]

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]

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