11 Replies Latest reply: Mar 26, 2018 12:57 PM by Steve Dark RSS

    Help with Filters

    Michelle Kallam

      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?

        • Re: Help with Filters
          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

          • Re: Help with Filters
            Michelle Kallam

            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
              • Re: Help with Filters
                Steve Dark

                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

                  • Re: Help with Filters
                    Michelle Kallam

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

                      • Re: Help with Filters
                        Michelle Kallam

                        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;

                          • Re: Help with Filters
                            Steve Dark

                            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

                              • Re: Help with Filters
                                Michelle Kallam

                                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;

                                  • Re: Help with Filters
                                    Steve Dark

                                    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

                        • Re: Help with Filters
                          Michelle Kallam

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