9 Replies Latest reply: Jul 13, 2016 6:57 PM by Marco Wedel RSS

    Age_Filter..??

    Silambarasan Manickam

      I want to filter age above 50 and over all(Not below 50).

      That must be called in a column (Column Name - Age_Filter, Column Values - Above 50 & OverAll).

      Please find the attached file.

        • Re: Age_Filter..??
          Sunny Talwar

          May be like this:

           

          LOAD Empid,

                    Empname,

                    Age,

                    If(Age > 50, 'Above 50 & OverAll') as Age_Filter

          From Source;

           

          If you need below 50 flag in Age_Filter, then try this:

          If(Age > 50, 'Above 50 & OverAll', 'Below 50') as Age_Filter

            • Re: Age_Filter..??
              Silambarasan Manickam

              Hi Sunny,

              I need values column values like 

              Age_Filter

              Above50 --> If i click Above50 it will give age above 50

              OverAll   --> if i click overall, should give overall data.

               

              Not like before u mentioned. Thank u..!!

                • Re: Age_Filter..??
                  Sunny Talwar

                  Try this approach:

                   

                  Table:

                  LOAD * INLINE [

                      Empid, Empname, Age

                      1, Abc, 23

                      2, Bcd, 45

                      3, Efg, 67

                      4, JK, 34

                      5, GJh, 89

                      6, JHk, 12

                      7, dss, 34

                  ];

                   

                  LinkTable:

                  LOAD 'Above 50' as Age_Filter,

                    Age

                  Resident Table

                  Where Age > 50;

                   

                  Concatenate(LinkTable)

                  LOAD 'OverAll' as Age_Filter,

                    Age

                  Resident Table;


                  Capture.PNG

                    • Re: Age_Filter..??
                      Sunny Talwar

                      To make this little better, I would pull only distinct values of Age in the LinkTable:

                       

                      Table:

                      LOAD * INLINE [

                          Empid, Empname, Age

                          1, Abc, 23

                          2, Bcd, 45

                          3, Efg, 67

                          4, JK, 34

                          5, GJh, 89

                          6, JHk, 12

                          7, dss, 34

                      ];

                       

                      LinkTable:

                      LOAD DISTINCT

                        'Above 50' as Age_Filter,

                        Age

                      Resident Table

                      Where Age > 50;

                       

                      Concatenate(LinkTable)

                      LOAD DISTINCT

                        'OverAll' as Age_Filter,

                        Age

                      Resident Table;

                      • Re: Age_Filter..??
                        Silambarasan Manickam

                        Yaa i got some idea now sunny.

                           for this small data file we can do this. if suppose i have a big data file(More Number records). if i concatenate it will take more time to load data. how can i proceed.

                         

                        Thanks..!!

                  • Re: Age_Filter..??
                    Silambarasan Manickam

                    Here is my coding,

                    Now I'm Clear with this.

                     

                    Table:

                    LOAD [Empid],

                      [Empname],

                      [Age],

                        if([Age]>50,'Above50') as Age_Filter

                    FROM [Age_Filter.xls]

                    (biff, embedded labels, table is Sheet1$) where [Age] >=50;

                     

                    Concatenate(Table)

                    LOAD [Empid],

                      [Empname],

                      [Age],

                        if([Age]>0,'OverAll') as Age_Filter

                    FROM [Age_Filter.xls]

                    (biff, embedded labels, table is Sheet1$);

                    • Re: Age_Filter..??
                      Nizam HM

                      i Think it will help you once u find useful mark it as helpful or correct

                      CrossTable(Field,Age_Filter,3)

                      LOAD Empid,

                           Empname,

                           Age,

                           if(Age>0,'OverAll') as AgeFilter,

                             if(Age>50,'Above50') as AgeFilter1

                          

                      FROM

                      Age_Filter1.xls

                      (biff, embedded labels, table is Sheet1$);

                      • Re: Age_Filter..??
                        Marco Wedel

                        Hi,

                         

                        some example for multiple age filters:

                         

                        QlikCommunity_Thread_224840_Pic2.JPG

                         

                        QlikCommunity_Thread_224840_Pic1.JPG

                         

                        QlikCommunity_Thread_224840_Pic3.JPG

                         

                         

                        tabEmps:
                        LOAD *,
                             Age>50 as [AgeFilter Above 50],
                             True() as [AgeFilter OverAll],
                             Age>20 as [AgeFilter Above 20],
                             Age<20 as [AgeFilter Below 20],
                             Age<45 as [AgeFilter Below 45],
                             Age>=45 and Age<=65 as [AgeFilter Middle Aged],
                             Age>65 as [AgeFilter Above 65];
                        LOAD Ceil(Rand()*80) as Age,
                             RecNo() as Empid,
                             KeepChar(Hash128(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Empname
                        AutoGenerate 100;
                        
                        tabTemp:
                        CrossTable (AgeFilterName, AgeFilterValue)
                        LOAD * Resident tabEmps;
                        
                        tabAgeFilter:
                        LOAD Distinct
                             Age,
                             SubField(AgeFilterName,'AgeFilter ',2) as AgeFilter
                        Resident tabTemp
                        Where AgeFilterValue and AgeFilterName like 'AgeFilter*';
                        
                        DROP Table tabTemp;
                        

                         

                        hope this helps

                         

                        regards

                         

                        Marco