12 Replies Latest reply: Sep 24, 2013 9:37 AM by Jan Croymans RSS

    Mixing InputFiles

      Hi,

       

      In the example below I try to mix different input files.
      From new files only new lines may be selected. When I use “
      WHERE NOT EXISTS (KEY)” nothing of file
      B will be selected. In my opinion the second line of file B should be selected.
      That’s what I want.

        //=====EXAMPLE=========================================================================
      [A]:
      LOAD * INLINE [
      DATE, TICKET, LINE, SHOPCODE, QTY, KEY
      05-06-2013, 100, 1, SH1, 10, SH1_100_1
      05-06-2013, 100, 2, SH1, 10, SH1_100_2
      05-06-2013, 101, 1, SH1, 10, SH1_101_1
      05-06-2013, 100, 1, SH2, 10, SH2_100_1
      ]
      ;

      [B]:
      NOCONCATENATE LOAD * INLINE [
      DATE, TICKET, LINE, SHOPCODE, QTY, KEY
      05-06-2013, 100, 1, SH1, 20, SH1_100_1
      05-06-2013, 102, 1, SH1, 10, SH1_102_1
      ]
      ;

      [SALES]:
      NOCONCATENATE LOAD
           
      DATE,
          
      TICKET,
          
      LINE,
          
      SHOPCODE,
          
      QTY,
           
      KEY
      RESIDENT [A];

      CONCATENATE LOAD
           
      DATE,
          
      TICKET,
          
      LINE,
          
      SHOPCODE,
          
      QTY,
           
      KEY
      RESIDENT [B]
      WHERE NOT EXISTS (KEY);

      DROP TABLE [A];
      DROP TABLE [B];

       

       

        • Re: Mixing InputFiles

          The command "exists" determines whether a specific field value exists in a specified field of the data loaded so far, not only in the last table.

           

          Change your script like this:

           

          [SALES]:

          NOCONCATENATE LOAD

                DATE,

               TICKET,

               LINE,

               SHOPCODE,

               QTY,

               KEY as KEY2

          RESIDENT [A];

           

          CONCATENATE LOAD

                DATE,

               TICKET,

               LINE,

               SHOPCODE,

               QTY,

               KEY as KEY2

          RESIDENT [B]

          WHERE NOT EXISTS ('KEY2',KEY);

          • Re: Mixing InputFiles
            Ralf Becher

            Hi Jan,

             

            the problem is that the keys already exists in table A/B. To solve this you have to rename the fields:

             

            [A]:
            LOAD * INLINE [
            DATE, TICKET, LINE, SHOPCODE, QTY, KEY_A
            05-06-2013, 100, 1, SH1, 10, SH1_100_1
            05-06-2013, 100, 2, SH1, 10, SH1_100_2
            05-06-2013, 101, 1, SH1, 10, SH1_101_1
            05-06-2013, 100, 1, SH2, 10, SH2_100_1
            ];
            
            [B]:
            NOCONCATENATE LOAD * INLINE [
            DATE, TICKET, LINE, SHOPCODE, QTY, KEY_B
            05-06-2013, 100, 1, SH1, 20, SH1_100_1
            05-06-2013, 102, 1, SH1, 10, SH1_102_1
            ];
            
            [SALES]:
            NOCONCATENATE LOAD
                  DATE,
                 TICKET,
                 LINE,
                 SHOPCODE,
                 QTY,
                  KEY_A as KEY
            RESIDENT [A];
            
            CONCATENATE LOAD
                  DATE,
                 TICKET,
                 LINE,
                 SHOPCODE,
                 QTY,
                  KEY_B as KEY
            RESIDENT [B]
            WHERE NOT EXISTS (KEY, KEY_B);
            
            DROP TABLE [A];
            DROP TABLE [B];
            
            
            
            
            

             

            - Ralf

            • Re: Mixing InputFiles

              Hi,

               

              Try using DISTINCT in your script and remove NOT EXISTS while concatenating as follows:

               

              [SALES]:

              NOCONCATENATE LOAD
                   
              DATE,
                 
              TICKET,
                 
              LINE,
                 
              SHOPCODE,
                 
              QTY,
                   
              KEY
              RESIDENT [A];

               

              CONCATENATE LOAD DISTINCT
                   
              DATE,
                 
              TICKET,
                 
              LINE,
                 
              SHOPCODE,
                 
              QTY,
                   
              KEY
              RESIDENT [B]

               

              Hope this helps!

              • Re: Mixing InputFiles
                Michael Jordan

                check attached file.

                 

                Thanks

                • Re: Mixing InputFiles
                  Stefan Wühl

                  Since you loaded all KEY values in tables A and B, the KEY exists for all values when reloading the table B in you concatenate Load.

                   

                  Either Load your data directly with the last two load statements (no preloading of field values), or use exists like this:

                   

                  [A]:

                  LOAD * INLINE [

                  DATE, TICKET, LINE, SHOPCODE, QTY, KEY

                  05-06-2013, 100, 1, SH1, 10, SH1_100_1

                  05-06-2013, 100, 2, SH1, 10, SH1_100_2

                  05-06-2013, 101, 1, SH1, 10, SH1_101_1

                  05-06-2013, 100, 1, SH2, 10, SH2_100_1

                  ];

                   

                   

                  [B]:

                  NOCONCATENATE LOAD * INLINE [

                  DATE, TICKET, LINE, SHOPCODE, QTY, KEY

                  05-06-2013, 100, 1, SH1, 20, SH1_100_1

                  05-06-2013, 102, 1, SH1, 10, SH1_102_1

                  ];

                   

                   

                  [SALES]:

                  NOCONCATENATE LOAD

                        DATE,

                       TICKET,

                       LINE,

                       SHOPCODE,

                       QTY,

                        KEY,

                        KEY as EXISTKEY

                  RESIDENT [A];

                  CONCATENATE LOAD

                        DATE,

                       TICKET,

                       LINE,

                       SHOPCODE,

                       QTY,

                        KEY

                  RESIDENT [B]

                  WHERE NOT EXISTS (EXISTKEY,KEY);

                   

                   

                  DROP TABLE [A];

                  DROP TABLE [B];

                  DROP FIELD EXISTKEY;

                  • Re: Mixing InputFiles
                    Michael Solomovich
                    Jan,
                    You got nothing because all values of KEY exist in B already.  Your example will work if you make a small change:

                    [SALES]:
                    NOCONCATENATE LOAD
                          DATE,
                         TICKET,
                         LINE,
                         SHOPCODE,
                         QTY,
                          KEY,
                         KEY as KEY1
                    RESIDENT [A];

                    CONCATENATE LOAD
                          DATE,
                         TICKET,
                         LINE,
                         SHOPCODE,
                         QTY,
                          KEY
                    RESIDENT [B]
                    WHERE NOT EXISTS (KEY1,KEY);
                    DROP FIELD KEY1;
                    ...

                    • Re: Mixing InputFiles
                      jagan mohan rao appala


                      Hi,

                       

                      Instead of reloading the tables again simply use this

                       

                       

                      [A]:

                       

                      LOAD

                       

                       

                      *

                      INLINE

                       

                      [

                       

                      [B]:

                       

                      NOCONCATENATE

                       

                       

                      LOAD

                      *

                      INLINE

                       

                      [

                       

                      WHERE

                       

                       

                      NOT

                      EXISTS

                      (KEY

                      )

                      ;

                       

                      Regards,

                      Jagan.

                      • Re: Mixing InputFiles
                        Byron Van Wyk

                        Hey Mate,

                         

                        Please see attached. I am actually not sure why your previous expression did not work and if someone could explain this behaviour with NOCONCATENATE INLINE LOADS, I would be grateful too

                         

                         

                        Cheers,

                        Byron

                        • Re: Mixing InputFiles
                          Erik van hout

                          [SALES]:

                          LOAD * INLINE [

                          DATE, TICKET, LINE, SHOPCODE, QTY, KEY

                          05-06-2013, 100, 1, SH1, 10, SH1_100_1

                          05-06-2013, 100, 2, SH1, 10, SH1_100_2

                          05-06-2013, 101, 1, SH1, 10, SH1_101_1

                          05-06-2013, 100, 1, SH2, 10, SH2_100_1

                          ];

                           

                          CONCATENATE LOAD * INLINE [

                          DATE, TICKET, LINE, SHOPCODE, QTY, KEY

                          05-06-2013, 100, 1, SH1, 20, SH1_100_1

                          05-06-2013, 102, 1, SH1, 10, SH1_102_1

                          ]

                          WHERE NOT EXISTS (KEY);

                           

                           

                          The INLINE loads create all kind of synthetic keys.

                          Above code will give you the result you're looking for.

                          • Re: Mixing InputFiles
                            Liron Baram

                            your mistake is

                            as you have table B then all keys are exists in the model already so nothing is reloaded

                            you can do it like this

                            [SALES]:
                            NOCONCATENATE LOAD
                                 
                            DATE,
                                
                            TICKET,
                                
                            LINE,
                                
                            SHOPCODE,
                                
                            QTY,
                                 
                            KEY AS NewKEY

                            CONCATENATE LOAD
                                 
                            DATE,
                                
                            TICKET,
                                
                            LINE,
                                
                            SHOPCODE,
                                
                            QTY,
                                 
                            KEY AS NewKEY
                            RESIDENT [B]
                            WHERE NOT EXISTS (NewKEY,KEY);

                            DROP TABLE [A];
                            DROP TABLE [B];

                            • Re: Mixing InputFiles
                              Rohit Koul

                              your Query is not clear bit confusing. what Out put exactly want ?

                              • Re: Mixing InputFiles

                                Thanks guys for your great help. Many answers where correct. It is clear to me now so I can finish my script.