Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I am doing a simple analysis of data, where I need to find each employee Role, using different data inputs. The problem I have is that for one of my data input (an excel file), after I have "left-jointed" the info to the main employee table, I have null values for those employees that does not appear in that table (that is expected), what I am seeking to do is just put an other Role found in other table, the problem in that the "isnull()" function appears to not be working, so I tried "len()", and it also failed. This doesnt happened for all of the registers, but for those who have the issue, I dont know what is the problem. The table looks like this:
CARGO | ROLCOSTOS2 | Isnull(CARGO) | len(CARGO) | |
aaguado@todo1.com | TD1VP | 1 | 0 | |
abareno@todo1.com | ESPECIALISTA DE DISEÑO DE SOLUCIONES | TD1EspecialistadeProductos | 0 | 36 |
abedoya@todo1.com | TD1Gerentedeproyecto | 1 | 0 | |
acamacho@todo1.com | DESARROLLADOR |
Here you can see how the Email "acamacho@todo1.com" shows a "DESARROLLADOR", but it does not show any len to it nor a isnull flag.
What should I do? btw the field "CARGO" is already TRIM
You need to do this manipulation after the two tables have been joined. Something along these lines
Table:
LOAD ....
FROM ....;
Left Join (Table)
LOAD ....
FROM ....;
FinalTable:
LOAD *,
If(Len(Trim(CARGO)) = 0, 'OTHER', CARGO) as NEW_CARGO
Resident Table;
DROP Table Table;
DROP Field CARGO;
Rename Field NEW_CARGO to CARGO;
Actually that is what I did look:
t:
load*,
if(len(CARGO)=0,ROLCOSTOS2,ROLHR) AS T,
len(CARGO),
Isnull(CARGO)
Resident ManodeObra;
DROP Table ManodeObra;
RENAME table t to ManodeObra;
drop field ROLHR;
RENAME FIELD T TO ROLHR;
...
Except for the "trim" it is the same, the problem is that it does not identify all the len(CARGO)=0 that are actually len=0, look:
CARGO | ROLCOSTOS2 | ROLHR | |
aaguado@todo1.com | TD1VP | TD1VP | |
abareno@todo1.com | ESPECIALISTADEDISEÑODESOLUCIONES | TD1EspecialistadeProductos | ESPECIALISTADEDISEÑODESOLUCIONES |
abedoya@todo1.com | TD1Gerentedeproyecto | TD1Gerentedeproyecto | |
acamacho@todo1.com | DESARROLLADOR |
You can see how it worked for two of them, but the last one is not picking up the length nor the Nul
I am having a hard time seeing it from the data base above, but can you share a screenshot of a table box with these fields?
t:
load *,
if(len(CARGO)=0,ROLCOSTOS2,ROLHR) AS T,
len(CARGO),
Isnull(CARGO)
Resident ManodeObra;
Yes, here is it:
Hi, Christopher Lopez.
Try use this logic with isnull().
t:
load*,
if(isnull(CARGO)=0
,ROLHR
,ROLCOSTOS2
) AS T,
len(CARGO),
Isnull(CARGO)
Resident ManodeObra;
DROP Table ManodeObra;
RENAME table t to ManodeObra;
drop field ROLHR;
RENAME FIELD T TO ROLHR;
Hope this helps!