8 Replies Latest reply: Aug 16, 2017 8:02 AM by kaan erisen RSS

    Script to get records based on null from tables.

    Supriya R

      Hi,

       

      i  have two  tables

       

      Table1:

      client_id campaign_id comm_type_id sent       delivered

      23             134                     44                234       230

      24             136                      46                     

      23              134                     45                 4         3

      23              135                     44               567       560

      24              136                     45               908        34

      23               134                     48                

      24                136                     46

       

      Table2:

      campaign_id   comm_type_id    sent_count   delivered

      134                     44                    230                  230

      136                      46                      76                  70

      134                     45                      4                       3

      135                     44                      566                 560

      136                     45                     907                   34

      134                     48                     40                      2

      136                     46                    20                      2

       

       

      Now you see there are no counts in Table1 for some rows, but you have those counts in Table2.

      So now i want to get those row empty counts in Table1 from Table2, but not those which are present in Table1.

       

      How could i do this Please help me on this

        • Re: Script to get records based on null from tables.
          Petter Skjolden

          Do you wish to have this done in the load script while getting data in or do you wish to do it with two tables that have already been loaded and do the result in a table in the UI?

            • Re: Script to get records based on null from tables.
              Supriya R

              HI,

              I want to do it in Load script

                • Re: Script to get records based on null from tables.
                  Petter Skjolden

                  A script like this should do the trick for you. You will have to do a LEFT JOIN between the two tables. LEFT JOIN makes all the rows of Table1 get all matching rows from Table2 to be joined by the common fields (fields that have exactly the same name):

                   

                  Table1:
                  LOAD
                  client_id,
                  campaign_id,
                  comm_type_id,
                  sent as sent1,
                  delivered as delivered1
                  INLINE [
                  client_id campaign_id comm_type_id sent       delivered
                  23  134  44  234  230
                  24  136  46 
                  23  134  45    4    3
                  23  135  44  567  560
                  24  136  45  908   34
                  23  134  48      
                  24  136  46
                  ] (delimiter is spaces);
                  
                  
                  LEFT JOIN
                  
                  
                  Table2:
                  LOAD
                  campaign_id,
                  comm_type_id,
                  sent_count as sent2,
                  delivered as delivered2
                  INLINE [
                  campaign_id   comm_type_id    sent_count   delivered
                  134  44  230  230
                  136  46   76   70
                  134  45    4    3
                  135  44  566  560
                  136  45  907   34
                  134  48   40    2
                  136  46   20    2
                  ] (delimiter is spaces);
                  
                  
                  DATA:
                  LOAD
                  client_id,
                  campaign_id,
                      comm_type_id,
                      if( IsNull(sent1) OR Trim(sent1)='' , sent2 , sent1 ) AS sent,
                      if( IsNull(delivered1) OR Trim(delivered1)='' , delivered2 , delivered1 ) AS delivered
                  RESIDENT
                  Table1;
                     
                  DROP TABLE Table1;
                  
                  

                   

                  You would probably read the two tables from external files I guess and then:

                   

                  Lines

                  8 to 17 would be replaced by something like this:

                   

                  FROM [Table1.xlsx] (ooxml.....) ;

                   

                  Lines 29 to 38 would be replace by:

                   

                  FROM [Table2.xlsx (ooxml....);

              • Re: Script to get records based on null from tables.
                kaan erisen

                How about this for non-join option:

                 

                MAP_TABLE:

                Mapping load

                campaign_id&'|'& comm_type_id AS LookUpKey,

                sent_count&'|'&delivered as Value

                inline [

                campaign_id,comm_type_id,sent_count,delivered

                134,44,230,230

                136,46,76,70

                134,45,4,3

                135,44,566,560

                136,45,907,34

                134,48,40,2

                136,46,20,2

                ];

                 

                 

                FACT:

                load

                client_id,

                campaign_id,

                comm_type_id,

                IF(LEN(trim(sent))>0,sent,SubField(ApplyMap('MAP_TABLE',campaign_id&'|'& comm_type_id),'|',1)) AS sent,

                IF(LEN(trim(delivered))>0,delivered,SubField(ApplyMap('MAP_TABLE',campaign_id&'|'& comm_type_id),'|',2)) AS delivered

                inline [

                client_id,campaign_id,comm_type_id,sent,delivered

                23,134,44,234,230

                24,136,46,,                   

                23,134,45,4,3

                23,135,44,567,560

                24,136,45,908,34

                23,134,48,,

                24,136,46,,

                ];

                  • Re: Script to get records based on null from tables.
                    Supriya R

                    Hi Kaan,

                     

                    What if i have a table with duplicate campaign_id and comm_type_id in Table2, at this time i should be able to sum sent and delivered counts

                     

                    Table2:

                    load * inline [

                    campaign_id,comm_type_id,sent_count,delivered

                    134,44,230,230

                    136,46,76,70

                    136,46,7,2

                    134,45,4,3

                    134,45,1,0

                    135,44,566,560

                    136,45,907,34

                    134,48,40,2

                    136,46,20,2

                    ];

                     

                    now i should be able to get sum of  sent_count and  delivered for campaign_id and comm_type_id. for that i used this


                     

                    MAP_TABLE:

                    MAPPING LOAD campaign_id&'|'& comm_type_id AS LookUpKey, sum(sent_count)&'|'& sum(delivered) as Value Resident Table2 group by campaign_id, comm_type_id;

                     

                    this gives me empty values in some fields. How could i approach this?

                      • Re: Script to get records based on null from tables.
                        kaan erisen

                        You should use LookUpKey as group by dimension.

                         

                        MAP_TABLE:

                        MAPPING LOAD campaign_id&'|'& comm_type_id AS LookUpKey, sum(sent_count)&'|'& sum(delivered) as Value Resident Table2

                        group by campaign_id&'|'& comm_type_id;

                         

                        But the real question is what should happen if there is a duplicate records on FACT table

                         

                        Table1:

                        client_id campaign_id comm_type_id sent      delivered

                        23            134                    44                234      230

                        24            136                      46               

                        23              134                    45                4        3

                        23              135                    44              567      560

                        24              136                    45              908        34

                        23              134                    48           

                        24                136                    46


                        Before looking values from Map table, Grouping the fact table is the best approach I guess


                        Script should be like this:


                        MAP_TABLE:

                        Mapping load

                        campaign_id&'|'& comm_type_id AS LookUpKey,

                        sum(sent_count)&'|'&sum(delivered) as Value

                        inline [

                        campaign_id,comm_type_id,sent_count,delivered

                        134,44,230,230

                        136,46,76,70

                        136,46,80,120

                        134,45,4,3

                        135,44,566,560

                        136,45,907,34

                        134,48,40,2

                        136,46,20,2

                        ]

                        Group By campaign_id&'|'& comm_type_id;

                         

                         

                         

                        fact:

                        Load

                        client_id,

                        campaign_id,

                        comm_type_id,

                        IF(sent<>0,sent,SubField(ApplyMap('MAP_TABLE',campaign_id&'|'& comm_type_id),'|',1)) AS sent,

                        IF(delivered<>0,delivered,SubField(ApplyMap('MAP_TABLE',campaign_id&'|'& comm_type_id),'|',2)) AS delivered

                        ;

                        load

                        client_id,

                        campaign_id,

                        comm_type_id,

                        sum(sent) as sent,

                        sum(delivered) as delivered

                        inline [

                        client_id,campaign_id,comm_type_id,sent,delivered

                        23,134,44,234,230

                        24,136,46,,                   

                        23,134,45,4,3

                        23,135,44,567,560

                        24,136,45,908,34

                        23,134,48,,

                        24,136,46,,

                        ]

                        Group By client_id,

                                campaign_id,

                                comm_type_id