4 Replies Latest reply: Aug 26, 2010 5:12 PM by Miguel Angel Baeyens de Arce RSS

    Problem with LOAD statement

      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 de Arce

          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

              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

                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 de Arce

                    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.