Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

diego1991
New 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
Honored Contributor II

Re: Script with grouped and detailed data

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.

5 Replies
agigliotti
Honored Contributor II

Re: Script with grouped and detailed data

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
New Contributor III

Re: Script with grouped and detailed data

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
Honored Contributor II

Re: Script with grouped and detailed data

which are the common fields between the two tables ?

how they should be linked together ?

diego1991
New Contributor III

Re: Script with grouped and detailed data

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
New Contributor III

Re: Script with grouped and detailed data

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.