4 Replies Latest reply: Nov 21, 2012 6:18 AM by Martin Grape RSS

    Adding flags to table

      I have a script where i want to add Yes/No-fields to the main customer table with data from a separate table. The relation to this table is 1 to 0/n.

       

      This script works, but is there perhaps a simpler/better way?

       

      Best regards

      Martin

       

      Customers:
      LOAD * INLINE 
      [
          CustomerId, Name
          1, John
          2, Ann
          3, Max
          4, Peter
          5, Helen
      ];
      Nix:
      LOAD * INLINE 
      [
          CustomerId, Type
          1, NixEmail
          1, NixTelephone
          3, NixTelephone
          5, NixEmail
      ];
      
      Temp:
      LOAD
         CustomerId
      RESIDENT
         Customers
      ;
       
      LEFT JOIN (Temp)
      LOAD
         CustomerId,
         'Yes' AS NixEmailTemp 
      RESIDENT   
         Nix
      WHERE
         Type = 'NixEmail'  
      ;
         
      LEFT JOIN (Temp)
      LOAD
         CustomerId,
         'Yes' AS NixTelephoneTemp 
      RESIDENT   
         Nix
      WHERE
         Type = 'NixTelephone'  
      ;
       
      LEFT JOIN (Customers)
       
      LOAD
         CustomerId,
         IF(NixEmailTemp = 'Yes', 'Yes', 'No')     AS NixEmail,
         IF(NixTelephoneTemp = 'Yes', 'Yes', 'No') AS NixTelephone
      RESIDENT
          Temp
      ;
       
      DROP TABLES Temp, Nix
      ;
      
        • Re: Adding flags to table
          Stefan Wühl

          Maybe like this, using Mapping instead of Joins:

           

          Customers:

          LOAD * INLINE

          [

              CustomerId, Name

              1, John

              2, Ann

              3, Max

              4, Peter

              5, Helen

          ];

           

           

          Nix:

          LOAD *, 'Yes' as Flag INLINE

          [

              CustomerId, Type

              1, NixEmail

              1, NixTelephone

              3, NixTelephone

              5, NixEmail

          ];

           

          MAP1:

          MAPPING LOAD CustomerId, 'Yes' Resident Nix where Type = 'NixEmail';

           

          MAP2:

          MAPPING LOAD CustomerId, 'Yes' Resident Nix where Type = 'NixTelephone';

           

          RESULT:

          LOAD

          CustomerId,

          Name,

          Applymap('MAP1', CustomerId, 'No') as NixEmail,

          Applymap('MAP2', CustomerId, 'No') as NixTelephone

          Resident Customers;

           

          drop tables Customers, Nix;

            • Re: Adding flags to table

              Thanks a lot for your input. I never tried Mapping before. Usually do most stuff in the database.

               

              I redid the script a little since i needed the days also since last nix per channel.

               

              So now the code looks a little like this. Is this optimal?

               

              Best regards

              Martin

               

              Nix:
              LOAD * INLINE 
              [
                  CustomerId, Type, DateType
                  1, NixEmail, 2012-11-21
                  1, NixTelephone, 2012-05-22
                  3, NixTelephone, 2011-12-12
                  5, NixEmail,2011-07-01
              ];
               
              MapNixEmail:
              MAPPING LOAD CustomerId, 'Yes' RESIDENT Nix WHERE Type = 'NixEmail';
               
              MapNixTelephone:
              MAPPING LOAD CustomerId, 'Yes' RESIDENT Nix WHERE Type = 'NixTelephone';
               
              MapNixEmailDays:
              MAPPING LOAD CustomerId, TODAY(1) - DateType RESIDENT Nix WHERE Type = 'NixEmail';
               
              MapNixTelephoneDays:
              MAPPING LOAD CustomerId, TODAY(1) - DateType RESIDENT Nix WHERE Type = 'NixTelephone';
               
              Customers:
              LOAD * INLINE 
              [
                  CustomerId, Name
                  1, John
                  2, Ann
                  3, Max
                  4, Peter
                  5, Helen
              ];
                
              LEFT JOIN (Customers)
               
              LOAD 
                 CustomerId, 
                 APPLYMAP('MapNixEmail', CustomerId, 'No')         AS NixEmail, 
                 APPLYMAP('MapNixEmailDays', CustomerId, '')       AS NixEmailDays,   
                 APPLYMAP('MapNixTelephone', CustomerId, 'No')     AS NixTelephone,
                 APPLYMAP('MapNixTelephoneDays', CustomerId, '')   AS NixTelephoneDays   
              RESIDENT 
                 Customers
              ;
               
              DROP TABLES Nix;
              
              
              
                • Re: Adding flags to table
                  Stefan Wühl

                  If I understand your data correctly, you should be able to get rid of the join and do the mapping while loading your customer data:

                   

                  Customers:

                  LOAD * ,

                    APPLYMAP('MapNixEmail', CustomerId, 'No')         AS NixEmail,

                     APPLYMAP('MapNixEmailDays', CustomerId, '')       AS NixEmailDays,  

                     APPLYMAP('MapNixTelephone', CustomerId, 'No')     AS NixTelephone,

                     APPLYMAP('MapNixTelephoneDays', CustomerId, '')   AS NixTelephoneDays  

                  INLINE

                  [

                      CustomerId, Name

                      1, John

                      2, Ann

                      3, Max

                      4, Peter

                      5, Helen

                  ];

                   

                  //LEFT JOIN (Customers)

                  //

                  //LOAD

                  //   CustomerId,

                  //   APPLYMAP('MapNixEmail', CustomerId, 'No')         AS NixEmail,

                  //   APPLYMAP('MapNixEmailDays', CustomerId, '')       AS NixEmailDays,  

                  //   APPLYMAP('MapNixTelephone', CustomerId, 'No')     AS NixTelephone,

                  //   APPLYMAP('MapNixTelephoneDays', CustomerId, '')   AS NixTelephoneDays  

                  //RESIDENT

                  //   Customers

                  //;

                   

                  DROP TABLES Nix;

                  exit script;