7 Replies Latest reply: Jun 30, 2015 6:16 AM by Ruben Marin RSS

    Script problem

      Hi,

      I dont want to use "left join", so I try the script codes as follows, but it don't work well, the table NOM_PRENOM_Equal is not exist.

      Some one can telle me why, thank you very much!

       

      QUALIFY *;

      REF:

      LOAD [ID],

           [Nom],

           Prénom

      FROM ...

      ;

       

      DOU:

      LOAD [ID] ,

           [Nom],

           Prénom

      FROM ...

      ;

       

      NOM_PRENOM_Equal:

      load [REF.ID]       as [ID],

           [REF.Nom]      as [Nom],

            REF.Prénom    as [Prénom],

           [DOU.ID]       as [ID dou],

           [DOU.Nom]      as [Nom dou],

            DOU.Prénom    as [Prénom dou]

      where ([REF.Nom] = [DOULON.Nom]) and (REF.Prénom = DOU.Prénom )

      ;

       

      drop table DOU;

      drop table REF;

        • Re: Script problem
          Stefan Wühl

          Your table NOM_PRENOM_Equal is missing an input table source (FROM ....)  and this can be only one table, not two.


          If you don't want to join, you can look into a MAPPING or LOOKUP solution.

          • Re: Script problem
            Peter Cammaert

            Do I get it right that the third LOAD statement tries to load from two tables at the same time? a LOAD statement has only one source.

             

            Also, it seems that the third LOAD has no source specification at all... You should at least specify a FROM..., RESIDENT... INLINE, AUTOGENERATE or FROM FIELD clause. Or add another LOAD/SELECT to create a PRECEDING LOAD.

             

            Peter

            • Re: Script problem
              Ruben Marin

              Hi Adami,

               

              NOM_PRENOM_Equal will not load as that table doesn't have any source (FROM, Resident...). If you use resident it will load from one previously loaded table so you'll need to use a join or applymap.

               

              As you're trying to load records where Nom and Prénom are equals you can try:

               

              MAP_DOU:

              Mapping LOAD Nom & '#' & Prénom, ID FROM...; //Dou table

               

              NOM_PRENOM_Equal:

              LOAD [ID],

                   [Nom],

                   Prénom,

                   [ID dou],

                   [Nom] as [Nom dou],

                   Prénom as [Prénom dou]

              Where not IsNull([ID dou];

              LOAD [ID],

                   [Nom],

                   Prénom,

                   ApplyMap('MAP_DOU', [Nom] & '#' & Prénom, Null()) as [ID dou]

              FROM ... // Ref table

               

              Not tested, hope this helps.