Age_Filter..??

[Silambarasan1306]

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.

[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

[Silambarasan1306]

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

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

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

[Silambarasan1306]

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

[Silambarasan1306]

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

[sunny_talwar]

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]

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

[MarcoWedel]

Hi,

some example for multiple age filters:

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