Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day to all of you, I would be glad if you can help me with the next problem:
I have an application in which I need to work with 3 different data sources (3 different monthly reports)
The first one is a report that includes USERS that have PORTFOLIO AGE different from zero
Table 1:
USER,
PORTFOLIO AGE,
REGION,
STATE,
PERIOD
in the second one I have the users from the first report but this one includes the CLASS they had for a certain period.
Table 2:
USER,
PERIOD,
CLASS
and in the final one I have a report that includes ALL of the USERS from the company but it isn't detailed, it is a grouped report
Table 3:
USER_QUANTITY,
REGION,
STATE,
CLASS
PERIOD
Because of that I'm having problems working with this app, because the Load crashes when it's creating the fields associations, I don't really know why this happens, so the idea I had to solve this is to group the data from table 1 and table 2 into a single grouped table.
Like doing a Keep between both tables but in which instead of user i can have USER_QUANTITY_2, the final result would be
Table 1_2:
USER_QUANTITY_2,
PORTFOLIO AGE,
REGION,
STATE,
PERIOD,
CLASS
How could I do that? or how else could I solve this problem?
Thanks a lot for reading the post and for helping me.
Diego Alejandro Vélez Becerra
i think you can do as below:
load
USER,
PORTFOLIO AGE,
REGION,
STATE,
PERIOD
from table1;
left join
load
USER,
PERIOD,
CLASS
from table2;
I hope it helps.
i think you can do as below:
load
USER,
PORTFOLIO AGE,
REGION,
STATE,
PERIOD
from table1;
left join
load
USER,
PERIOD,
CLASS
from table2;
I hope it helps.
Hi Andrea, thanks a lot for your quick answer, I tried it but the CLASS field is only getting combined in the first PERIOD "201801" in the other ones it isn't, as it can be seen in the next image.
My script is the next one:
Buffer (Incremental) LOAD
if(SUCURSAL=1000,'1. CUCUTA',
if(SUCURSAL=2000,'2.PAMPLONA',
if(SUCURSAL=3000,'3. OCAÑA',
if(SUCURSAL=4000,'4. TIBÚ','5. AGUACHICA')))) as SUCURSAL,
USUARIO,
ATRASOS,
if(mid(RUTA,6,2)='','xx',mid(RUTA,6,2)) as SECTOR,
"ESTADO DE SUMINISTRO",
filebasename() as PERIODO
FROM [lib://COMERCIAL_CARTERA/*.csv*]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
Left Join
LOAD
NUMERO_CLIENTE as USUARIO,
filebasename() as PERIODO,
CLASE_SER
FROM [lib://CLASS/*.csv*]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
LOAD
CLASE_SER,
"CLASE DE SERVICIO"
FROM [lib://clasi_CLASS/clase_ventas.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
Any idea about why it doesn't' work? thanks a lot.
which are the common fields between the two tables ?
how they should be linked together ?
Hi Andrea,
The common fields between table1 and table 2 should be "USUARIO" and "PERIODO",
For each "USUARIO" in every "PERIODO" they have a "CLASE_SER" register that could change from one period to other one.
Thanks again.
Andrea, thanks a lot, I had to do a little modifications in the data Load to solve it, and the Left Join helped me a lot with the cross between tables, also used an Outer Join for the problem between some data being in Table 1 and not in Table 2 and viceversa, Thanks a lot again.