Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

silambarasan130
Contributor II

Age_Filter..??

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.

9 Replies
MVP
MVP

Re: Age_Filter..??

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

silambarasan130
Contributor II

Re: Age_Filter..??

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..!!

MVP
MVP

Re: Age_Filter..??

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

MVP
MVP

Re: Age_Filter..??

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;

silambarasan130
Contributor II

Re: Age_Filter..??

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..!!

silambarasan130
Contributor II

Re: Age_Filter..??

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$);

MVP
MVP

Re: Age_Filter..??

This is not what you should do. This is duplicating all the data. Create a distinct list of Age in a separate table and then create your Age_Filter flag. Doing this, you would run into many different issue.

nizamsha
Valued Contributor

Re: Age_Filter..??

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..??

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