Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
diego1991
Contributor III
Contributor III

Script with grouped and detailed data

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

1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

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.

View solution in original post

5 Replies
agigliotti
Partner - Champion
Partner - Champion

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.

diego1991
Contributor III
Contributor III
Author

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.

class.PNG

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.

agigliotti
Partner - Champion
Partner - Champion

which are the common fields between the two tables ?

how they should be linked together ?

diego1991
Contributor III
Contributor III
Author

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.

diego1991
Contributor III
Contributor III
Author

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.