Re: problem with ETL - Qlik Community

Aleja54 · ‎2019-04-05

I have a problem with the data load, I can put the tables together and show all, except one in which it tells me exhausted memory, but if I enter other fields it does not make me problems, but if with tbl_defad, something I do wrong no, here goes the code :

LIB CONNECT TO 'mySQL Database Conection2';

[tbl_deccs]:
LOAD `ccosto`,
	`DECCS_NUMFACT`,
    `DECCS_FECHACC`,
    `DECCS_NUMDEP`,
    `codprog`,
    `DECCS_RUTPROV`
      
FROM [lib://mySQL Database Conection2/provi2/tbl_deccs.qvd](qvd);



LIB CONNECT TO 'mySQL Database Conection2';
left keep(tbl_deccs)
LOAD `codprog`,
    `TPROG_NOMPROG`

FROM [lib://mySQL Database Conection2/provi2/tbl_tprog.qvd](qvd);

STORE tbl_tprog INTO lib://mySQL Database Conection2/provi3/tbl_tprog.qvd(qvd);

Left keep (tbl_tprog)
LOAD `ccosto`,
	`TCCOS_ANO`,
    `TCCOS_NOMDEPTO`

FROM [lib://mySQL Database Conection2/provi2/tbl_tccos.qvd](qvd); 
STORE tbl_tccos INTO lib://mySQL Database Conection2/provi3/tbl_tccos.qvd(qvd);
Drop Table tbl_tccos;
STORE tbl_deccs INTO lib://mySQL Database Conection2/provi3/tbl_deccs.qvd(qvd);

Drop Table tbl_deccs;


Drop Table tbl_tprog;
LIB CONNECT TO 'mySQL Database Conection2';
2
[tbl_deta]:
3

4
LOAD `cuenta`, 
5
    `DETA_GLOSA`,   
6
    `numcc`,
7
    `rutprov`,
8
    `DETA_TIPOCC`,
9
    `tipodoc`,
10
    `area`, 
11
    div(`DETA_DEBE`, 10) ,
12
    `DETA_NUMFACT`,
13
    
14
    `DETA_FECHACC`,
15
    AutonumberHash128(DETA_NUMFACT, trim(rutprov)) as %compkey,
16
    AutonumberHash128(numcc, rutprov, DETA_NUMFACT) as %compkey4,
17
    AutonumberHash128(DETA_NUMFACT, rutprov) as %compkey3,
18
    AutonumberHash128(rutprov, DETA_NUMFACT, tipodoc) as %compkey2,
19
    AutonumberHash128(rutprov, DETA_NUMFACT) as %compkey5
20
    
21
FROM [lib://mySQL Database Conection2/provi2/tbl_deta.qvd](qvd);
22

23
left keep(tbl_deta)
24
LOAD area,
25
    nombre_areas
26
FROM [lib://mySQL Database Conection2/provi2/areas.qvd](qvd); 
27
STORE areas INTO lib://mySQL Database Conection2/provi3/areas.qvd(qvd);
28
Drop Table areas;
29

30
left keep(tbl_deta)
31

32
LOAD `ANTIC_ESTADO`,
33
    `ANTIC_NUMFAC`,
34
    `ANTIC_NUMOB_ORI`,
35
    `ANTIC_NUMOOBL`,
36
    `ANTIC_RUTPROV`,
37
    `ANTIC_TIPOB_ORI`,
38
    `ANTIC_TIPOFAC`,
39
    `ANTIC_TIPOOBL`,
40
    `ANTIC_ANO`,
41
    `ANTIC_AREAS`,
42
    `ANTIC_AUTORIZADO`,
43
    `ANTIC_PAGADO`,

`ANTIC_RENDIDO`,
    AutonumberHash128(ANTIC_RUTPROV, ANTIC_NUMFAC) as %compkey5
FROM [lib://mySQL Database Conection2/provi2/tbl_antic.qvd](qvd); 
STORE tbl_antic INTO lib://mySQL Database Conection2/provi3/tbl_antic.qvd(qvd);
Drop Table tbl_antic;

left keep(tbl_deta)

LOAD `DETDP_ESTADO`,
    `DETDP_NUMDECRE`,
    `DETDP_NUMEGRE`,
    `DETDP_NUMFACT`,
    `DETDP_RUTPROV`,
    `DETDP_TIPOFAC`,
    `DETDP_ANO`,
    `DETDP_AREAS`,
    `DETDP_VALDEC`,
    AutonumberHash128(DETDP_RUTPROV, DETDP_NUMFACT, DETDP_TIPOFAC) as %compkey2
FROM [lib://mySQL Database Conection2/provi2/tbl_detdp.qvd](qvd); 
STORE tbl_detdp INTO lib://mySQL Database Conection2/provi3/tbl_detdp.qvd(qvd);
Drop Table tbl_detdp;

    
Left keep (tbl_deta)
LOAD `DEFAD_CCOSTO`,
    `DEFAD_CODPROG` ,
    `DEFAD_NUMCOM`,
    `DEFAD_NUMFACT`,
    `DEFAD_RUTPROV` ,
    `DEFAD_TIPOCOM`,
    `compromiso`,
    AutonumberHash128(DEFAD_NUMFACT, trim(DEFAD_RUTPROV)) as %compkey3
FROM [lib://mySQL Database Conection2/provi2/tbl_defad.qvd](qvd); 
STORE tbl_defad INTO lib://mySQL Database Conection2/provi3/tbl_defad.qvd(qvd);
Drop Table tbl_defad;


LIB CONNECT TO 'mySQL Database Conection2';
left keep(tbl_deta)
LOAD `MPROV_NOMPROV`,
    `rutprov`
FROM [lib://mySQL Database Conection2/provi2/tbl_mprov.qvd](qvd);
STORE tbl_mprov INTO lib://mySQL Database Conection2/provi3/tbl_mprov.qvd(qvd);
Drop Table tbl_mprov;


LIB CONNECT TO 'mySQL Database Conection2';
left keep(tbl_deta)
LOAD `TTDOC_NOMDOC`,
    `tipodoc`
FROM [lib://mySQL Database Conection2/provi2/tbl_ttdoc.qvd](qvd);
STORE tbl_ttdoc INTO lib://mySQL Database Conection2/provi3/tbl_ttdoc.qvd(qvd);
Drop Table tbl_ttdoc;

LIB CONNECT TO 'mySQL Database Conection2';
left keep(tbl_deta)
LOAD `cuenta`,
    `MCUEN_NOM_CTA`
FROM [lib://mySQL Database Conection2/provi2/tbl_mcuen.qvd](qvd);
STORE tbl_mcuen INTO lib://mySQL Database Conection2/provi3/tbl_mcuen.qvd(qvd);
Drop Table tbl_mcuen;

LIB CONNECT TO 'mySQL Database Conection2';

left keep(tbl_deta)
LOAD `ccosto`,
	`DECCS_NUMFACT`,
    
    `DECCS_FECHACC`,
    `DECCS_NUMDEP`,
    `codprog`,
    `DECCS_RUTPROV`,
    AutonumberHash128(DECCS_NUMFACT, trim(DECCS_RUTPROV)) as %compkey
      
FROM [lib://mySQL Database Conection2/provi2/tbl_deccs.qvd](qvd);

STORE tbl_deccs INTO lib://mySQL Database Conection2/provi3/tbl_deccs.qvd(qvd);
Drop Table tbl_deccs;
LIB CONNECT TO 'mySQL Database Conection2';
left keep(tbl_deta)

LOAD `CONBP_NUMEROCC`,
	`CONBP_RUTPROV`,
    `CONBP_SERIE`,
    `CONBP_FECHA`,
    `CONBP_MONTO`,
    `CONBP_NUMCHEQ`,
    `CONBP_NUMMOV`,
     `CONBP_NUMFAC`,
    AutonumberHash128(CONBP_NUMEROCC, CONBP_RUTPROV, CONBP_NUMFAC) as %compkey4
FROM [lib://mySQL Database Conection2/provi2/tbl_conbp.qvd](qvd);
STORE tbl_conbp INTO lib://mySQL Database Conection2/provi3/tbl_conbp.qvd(qvd);
Drop Table tbl_conbp;

 
    
 STORE tbl_deta INTO lib://mySQL Database Conection2/provi3/tbl_deta.qvd(qvd);
Drop Table tbl_deta;

Lisa_P · ‎2019-04-08

Without knowing your data, it sounds like an issue with the data model, can you send a screenshot from the data model viewer ?

Aleja54 · ‎2019-04-08

hello, thanks for answering, sorry, here I send a screenshot of my model

Aleja54 · ‎2019-04-08

hello, thanks for answering, sorry, here I send a screenshot of my model

dwforest · ‎2019-04-08

It is possible you are running out of memory.... have you tried debugging and limiting the number of records?
Also as it loads without the limit, are the number of rows loaded correct?
If you want the tables to remain separate, there is no need to join them with keep, just don't join them. Qlik does the linking automatically based on field name.

Marcos_rv · ‎2019-04-08

I would separate everything, first create different levels of folders, first a folder that is called DATA, in this you will make the select * of the tables you need, followed by that you make a store and a drop, this is for all tables that are in the database.

Step 2, arms a qvw where you will assemble all the tables, here you will call the qvd that you created in the previous step and you will do all the joins operations, but do not use the "keep", I prefer that you use the Exists (), with this your application will be more perfomance.

and the arms that you keep are stored in the STG folder, you are going to make the stores and drops.

in the final app raising all the qvds of the STG you have to keep the model clean.

They are good practices, it is more because the code that you show us is really illegible.
Regards!!!

problem with ETL

ETL

OPTIMIZATION