topic Problem with LOAD statement in QlikView

Problem with LOAD statement

Thu, 26 Aug 2010 16:37:46 GMT

Hi Community,

I have wrote my script:

Libelle:
LOAD Réf.,
ACTIF
FROM
Datasources\DSF01.xlsx
(ooxml, embedded labels, table is Feuil1);

/*****************/

Chiffre:
LOAD BRUTTEST,
AMOR_PROVTEST,
NETTEST,
NET_MOINS_UNTEST;

SQL SELECT distinct NULL BRUTTEST, NULL AMOR_PROVTEST, NULL NETTEST, NULL NET_MOINS_UNTEST

FROM DELTAERP.COMECRGEN egt

WHERE egt.cdos = '01'

UNION ALL

SELECT nvl(SUM(egi.cpt_mtdb),0) BRUTTEST, NULL AMOR_PROVTEST, 0 NETTEST, 0 NET_MOINS_UNTEST

FROM DELTAERP.COMECRGEN egi

LEFT JOIN DELTAERP.COMECR ei ON (ei.cdos = egi.cdos) AND (egi.numsai = ei.numsai)

WHERE egi.cdos = '01' AND ((egi.numcpt LIKE '201%') OR (egi.numcpt LIKE '202%') OR (egi.numcpt LIKE '206%'))

UNION ALL

SELECT nvl(SUM(eg.cpt_mtdb),0) BRUTTEST, NULL AMOR_PROVTEST, 0 NETTEST, 0 NET_MOINS_UNTEST

FROM DELTAERP.COMECRGEN eg

LEFT JOIN DELTAERP.COMECR e ON e.cdos = eg.cdos AND eg.numsai = e.numsai

WHERE (eg.cdos = '01') AND (eg.numcpt LIKE '201%')

UNION ALL

SELECT nvl(SUM(eg1.cpt_mtdb),0) BRUTTEST, NULL AMOR_PROVTEST, 0 NETTEST, 0 NET_MOINS_UNTEST

FROM DELTAERP.COMECRGEN eg1

LEFT JOIN DELTAERP.COMECR e1 ON e1.cdos = eg1.cdos AND eg1.numsai = e1.numsai

WHERE eg1.cdos = '01' AND eg1.numcpt LIKE '202%'

UNION ALL

SELECT nvl(SUM(eg2.cpt_mtdb),0) BRUTTEST, NULL AMOR_PROVTEST, 0 NETTEST, 0 NET_MOINS_UNTEST

FROM DELTAERP.COMECRGEN eg2

LEFT JOIN DELTAERP.COMECR e2 ON e2.cdos = eg2.cdos AND eg2.numsai = e2.numsai

WHERE eg2.cdos = '01' AND eg2.numcpt LIKE '206%';

/*****************/

Libelle_Chiffre:
LOAD Réf. AS Réf.,
ACTIF AS ACTIF
FROM Libelle;
JOIN
LOAD BRUTTEST AS BRUTTEST,
AMOR_PROVTEST AS AMOR_PROVTEST,
NETTEST AS NETTEST,
NET_MOINS_UNTEST AS NET_MOINS_UNTEST
FROM Chiffre;

When i launche the reloading, the first two LOAD statement execute well.

At the moment, to run the third LOAD statement, the following error message appears.

Impossible d'ouvrir le fichier'I:\CAMEROUN DELTA INFORMATIQUE\Projet BGFI\Libelle'
Libelle_Chiffre:
LOAD Réf. AS Réf.,
ACTIF AS ACTIF
FROM Libelle

Unable to open the file 'I:\CAMEROUN DELTA INFORMATIQUE\Projet BGFI\Libelle'
Libelle_Chiffre:
LOAD Réf. AS Réf.,
ACTIF AS ACTIF
FROM Libelle

When i press the OK button of the Dialog box of error message

The following second message appears:

Impossible d'ouvrir le fichier'I:\CAMEROUN DELTA INFORMATIQUE\Projet BGFI\Chiffre'
JOIN
LOAD BRUTTEST AS BRUTTEST,
AMOR_PROVTEST AS AMOR_PROVTEST,
NETTEST AS NETTEST,
NET_MOINS_UNTEST AS NET_MOINS_UNTEST
FROM Chiffre

Unable to open file 'I:\CAMEROUN DELTA INFORMATIQUE\Projet BGFI\Chiffre'
JOIN
LOAD BRUTTEST AS BRUTTEST,
AMOR_PROVTEST AS AMOR_PROVTEST,
NETTEST AS NETTEST,
NET_MOINS_UNTEST AS NET_MOINS_UNTEST
FROM Chiffre

But after running the first two LOAD Statement, the tables called 'Libelle' and 'Chiffre' are already created.

Please i want an help. it's so urgent.

Thank you.

Problem with LOAD statement

Miguel_Angel_Baeyens — Thu, 26 Aug 2010 17:33:07 GMT

Hello Yimen,

When you load from an already existing table, you need to use RESIDENT keyword instead of FROM, to get something like

JOINLOAD BRUTTEST ... RESIDENT Chiffre;

Re. :Re: Problem with LOAD statement

Thu, 26 Aug 2010 17:39:55 GMT

Please, when i use the key word RESIDENT, there is no errors but the Result Table is not created. Because when i want to map Objet Table to this table, it not appears in the list of residents tables

What must i do??

Re. :Re: Problem with LOAD statement

Thu, 26 Aug 2010 17:59:09 GMT

In fact, i remark that, JOIN and RESIDENT do the combination of element, and then the CONCATENATE add the records of the second table at the end of first table.

But i want to join end to end the tables TAB1 and TAB2 with theirs data.

Re. :Re: Problem with LOAD statement

Miguel_Angel_Baeyens — Thu, 26 Aug 2010 21:12:28 GMT

Of course it depends on your data model and what do you want to achieve. Take into account the following

1.- When you create a new table using tables that already exist, you may probably rename the fields of the new table, as it can result on joins or synthetic keys your probably don't need

2.- You can use the JOIN statement instead of the RESIDENT as in my sample above, just remember that JOINing in QlikView requires that both tables share one field (same fieldname in both tables)

Regards.

EDIT: The error message that says that Chiffre table does not exist doesn't mean actually a table, but a file, because you don't have stored your tables into files, you you have to retrieve information from them using the RESIDENT clause. With that, you will avoid the error you are getting.

On the other hand, if the new table consist of the same fields with same names that any of the already loaded tables, you should use NOCONCATENATE keyword before the LOAD, as QlikView implicitly concatenates two tables when they are loaded one just after the other and contain the same number of fields and those are named alike.

I see that you are joining two tables. You need to one field to be renamed equally in both tables. Apart from that, you don't need to rename fields if the right part of the AS clause is identical to the left part.