Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone.
We are creating a dashboard that compares different databases with the same information. We start from a database that would be the master, and compare other databases information with the master. Currently we load all the data, and with a pivot table, we have constraints "if" to mark as the values are identical between databases with an OK or with a NOK when the compare fails.
Example:
master:
id, nombre, mail
001, aaa 111, aaa@aaa.com
002, bbb 222, bbb@bbb.com
003, ccc 333, ccc@ccc.com
tbl001
id, nombre, mail
001, abc 111, aaa@aaa.com
002, bbb 222, bbb@xxx.com
003, ccc 333, ccc@ccc.com
We would like, into the LOAD statement, compare every
What we would like is that, within the LOAD, comparing each of the values in the table tbl001 against the master:
- if tbl001.nombre when id 001 is different of master.nombre when id is 001, then check_nombre = -1, else check_nombre = 0.
- if tbl001.mail when id 001 is different of master.mail when id is 001, then check_mail = -1, else check_mail = 0.
Thanks in advance.
------------------------------------------------------------------------
Hola a todos.
Estamos creando un cuadro de mandos que compara diferentes bases de datos con misma información. Partimos de una base de datos que sería la master, y comparamos la información con la master. Actualmente cargamos los datos, y con una tabla pivotante, tenemos condicionantes "if" para marcar como que los valores son idénticos entre bases de datos con un OK o con un NOK si no coiciden.
Ejemplo:
master:
id, nombre, mail
001, aaa 111, aaa@aaa.com
002, bbb 222, bbb@bbb.com
003, ccc 333, ccc@ccc.com
tbl001
id, nombre, mail
001, abc 111, aaa@aaa.com
002, bbb 222, bbb@xxx.com
003, ccc 333, ccc@ccc.com
Lo que nos gustaria es que, dentro del LOAD, comparara cada uno de los valores de la tabla tbl001 contra la master:
- Si tbl001.nombre cuando id 001 es diferente a master.nombre cuando id es 001, entonces check_nombre = -1, si no check_nombre = 0.
- Si tbl001.mail cuando id 001 es diferente a master.mail cuando id es 001, entonces check_mail = -1, si no check_mail = 0.
Muchísimas gracias de antemano.
Are you looking for this?
If yes, then look at the following script:
master:
LOAD * Inline [
id, nombre, mail
001, aaa 111, aaa@aaa.com
002, bbb 222, bbb@bbb.com
003, ccc 333, ccc@ccc.com
];
tbl001:
NoConcatenate
LOAD * Inline [
id, nombre, mail
001, abc 111, aaa@aaa.com
002, bbb 222, bbb@xxx.com
003, ccc 333, ccc@ccc.com
];
Temp:
NoConcatenate
LOAD *
Resident master;
Join(Temp)
LOAD id,
nombre as nombre1,
mail as mail1
Resident tbl001;
Join(Temp)
LOAD id,
If(nombre1 = nombre, 0, -1) as check_nombre,
If(mail = mail1, 0, -1) as check_mail
Resident Temp;
DROP Tables master, tbl001;
Are you looking for this?
If yes, then look at the following script:
master:
LOAD * Inline [
id, nombre, mail
001, aaa 111, aaa@aaa.com
002, bbb 222, bbb@bbb.com
003, ccc 333, ccc@ccc.com
];
tbl001:
NoConcatenate
LOAD * Inline [
id, nombre, mail
001, abc 111, aaa@aaa.com
002, bbb 222, bbb@xxx.com
003, ccc 333, ccc@ccc.com
];
Temp:
NoConcatenate
LOAD *
Resident master;
Join(Temp)
LOAD id,
nombre as nombre1,
mail as mail1
Resident tbl001;
Join(Temp)
LOAD id,
If(nombre1 = nombre, 0, -1) as check_nombre,
If(mail = mail1, 0, -1) as check_mail
Resident Temp;
DROP Tables master, tbl001;
Hi sunindia!
You are right!
Thank you so much!
No problem
I am glad I was able to help.
Best,
Sunny
master:
LOAD * Inline
[
id, nombre, mail
001, aaa 111, aaa@aaa.com
002, bbb 222, bbb@bbb.com
003, ccc 333, ccc@ccc.com
];
Join
tbl001:
LOAD id,nombre as nombretab001, mail as mailtab001
;
LOAD * Inline
[
id, nombre, mail
001, abc 111, aaa@aaa.com
002, bbb 222, bbb@xxx.com
003, ccc 333, ccc@ccc.com
];
NoConcatenate
final:
LOAD id,nombretab001,mailtab001,nombre, mail,
if(nombretab001=nombre,0,-1) as check_nombre ,
if(mailtab001=mail,0,-1) as check_mail
Resident master;
drop Table master;