Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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..!!
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;
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;
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..!!
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$);
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.
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$);
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