- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Best way to assign a Category depending in the value of two Columns
Hi Everyone, I have a Issue and i need some help:
I have two tables:
Table 1:
Id Category Min Max
1 1 1 2
2 2 2 5
Table 2:
Category Days
1 3
2 1
I have to assign a Category taking the Column Days of Table 2 but depending on the range of the columns Min and Max of Table 1.
I will appreciate all help that you can give me.
Regards
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Carlos,
I am not sure if you are looking for the below one.
Categories:
LOAD * INLINE [
n_Id_Tramite, Category, Min_Days, Max_Days
1, Menos de 24hrs, , 1
1, Rango 1, 1, 2
1, Rango 2, 3, 5
1, Rango 3, 6, 10
1, Rango 4, 11, 12
1, Rango 5, 13, 20
1, Rango 6, 21, 30
1, Mas de 30 dias, 30
2, Menos 24hrs, , 1
2, Rango 1, 1, 5
2, Rango 2, 6, 7
2, Rango 3, 8, 12
2, Rango 4, 13, 20
2, Rango 5, 21, 30
2, Mas de 30 dias, 30
];
Process:
LOAD * INLINE [
n_Id_Tramite, Process, Days
1, 1, 5
1, 2, 12
1, 3, 14
2, 4, 15
2, 5, 2
1, 6, 100
];
inner join IntervalMatch(Days,n_Id_Tramite) load Min_Days,Max_Days,n_Id_Tramite Resident Categories;
Attached the file and the output, just check it out.
Regards,
Sri
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
See if this helps at all. I'm not too clear on what you are looking for.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Rebeccad,
Thanks for your help but i would like to know if it is possible to put instead 'In Range' put the Category of table 1, something like this:
if(DaysField<=MaxField and DaysField>=MinField, Category From Table 1, 'With out Category')
I hope make myself clear.
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just adjust the expression I put in 'In Range?' to:
if(DaysField<=MaxField and DaysField>=MinField,Category,'Without Category')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I cant put just like that because I miss to put that i have another conditional field:
My real table is like this:
n_Id_Tramite | Category | Min_Days | Max_Days |
1 | Menos de 24hrs | 1 | |
1 | Rango 1 | 1 | 2 |
1 | Rango 2 | 3 | 5 |
1 | Rango 3 | 6 | 10 |
1 | Rango 4 | 11 | 12 |
1 | Rango 5 | 13 | 20 |
1 | Rango 6 | 21 | 30 |
1 | Mas de 30 días | 30 | |
2 | Menos 24hrs | 1 | |
2 | Rango 1 | 1 | 5 |
2 | Rango 2 | 6 | 7 |
2 | Rango 3 | 8 | 12 |
2 | Rango 4 | 13 | 20 |
2 | Rango 5 | 21 | 30 |
2 | Más de 30 días | 30 |
And I have other table like this:
n_Id_Tramite | Process | Days |
1 | 1 | 5 |
1 | 2 | 12 |
1 | 3 | 14 |
2 | 4 | 15 |
2 | 5 | 2 |
1 | 6 | 100 |
Now the question is How i can assign table 2 a category depending on the Min and Max Columns of table 1
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Carlos,
I am not sure if you are looking for the below one.
Categories:
LOAD * INLINE [
n_Id_Tramite, Category, Min_Days, Max_Days
1, Menos de 24hrs, , 1
1, Rango 1, 1, 2
1, Rango 2, 3, 5
1, Rango 3, 6, 10
1, Rango 4, 11, 12
1, Rango 5, 13, 20
1, Rango 6, 21, 30
1, Mas de 30 dias, 30
2, Menos 24hrs, , 1
2, Rango 1, 1, 5
2, Rango 2, 6, 7
2, Rango 3, 8, 12
2, Rango 4, 13, 20
2, Rango 5, 21, 30
2, Mas de 30 dias, 30
];
Process:
LOAD * INLINE [
n_Id_Tramite, Process, Days
1, 1, 5
1, 2, 12
1, 3, 14
2, 4, 15
2, 5, 2
1, 6, 100
];
inner join IntervalMatch(Days,n_Id_Tramite) load Min_Days,Max_Days,n_Id_Tramite Resident Categories;
Attached the file and the output, just check it out.
Regards,
Sri
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks A lot Srikarch!!!
It works fine.
Regards