6 Replies Latest reply: Nov 1, 2016 2:03 PM by Massimo Grossi RSS

    Two Similar Fields needed to be one

    mark pratt

      So I need the following:

       

      LOAD

        "Account No",

          "Product Category Name",

          "Account Status",

          "Branch Name",

          "EOD Code",

          "Primary Customer - ID",

          "Secondary Customer - ID";

      SQL SELECT

        "Account No",

          "Product Category Name",

          "Account Status",

          "Branch Name",

          "EOD Code",

          "Primary Customer - ID",

          "Secondary Customer - ID"

      FROM

      Test.dbo."Accounts"

      Where

        "Account Status" <> 'Closed'

      and

        "EOD Code" < '1';

       

      To act as though the primary and secondary Customer-ID is just Customer-ID without any data loss. I will also be doing a Load Group by Customer-ID once I get this figured out.

       

      Thanks

        • Re: Two Similar Fields needed to be one
          Massimo Grossi

          What do you mean for

          the primary and secondary Customer-ID is just Customer-ID without any data loss.

          Could you post an examèle of the result you want?

            • Re: Two Similar Fields needed to be one
              mark pratt

              Meaning I don't want it separated by primary and secondary. I want the result to be just customer id with the other fields so say an account has both primary and secondary customer ids it would return as separate row with customer id (primary) as one row with the other fields and then customer id (secondary) with the same fields. Hope that makes sense, basically doing away with the builtin hierarchy and having just customer id field.

               

              Thanks

                • Re: Two Similar Fields needed to be one
                  Vivek Nair

                  why don't you modify your sql as below:

                  SQL SELECT

                    "Account No",

                      "Product Category Name",

                      "Account Status",

                      "Branch Name",

                      "EOD Code",

                      "Primary Customer - ID" as customer id

                  FROM

                  Test.dbo."Accounts"

                  Where

                    "Account Status" <> 'Closed'

                  and

                    "EOD Code" < '1'
                   
                    union
                    SELECT

                    "Account No",

                      "Product Category Name",

                      "Account Status",

                      "Branch Name",

                      "EOD Code",

                     "Secondary Customer - ID" as customer id

                    

                  FROM

                  Test.dbo."Accounts"

                  Where

                    "Account Status" <> 'Closed'

                  and

                    "EOD Code" < '1';

                  • Re: Two Similar Fields needed to be one
                    Massimo Grossi

                    TB:

                    LOAD

                      "Account No",

                        "Product Category Name",

                        "Account Status",

                        "Branch Name",

                        "EOD Code",

                        "Primary Customer - ID" as [Customer-ID],

                        "Secondary Customer - ID"

                         ;

                    SQL SELECT

                      "Account No",

                        "Product Category Name",

                        "Account Status",

                        "Branch Name",

                        "EOD Code",

                        "Primary Customer - ID",

                        "Secondary Customer - ID"

                    FROM

                    Test.dbo."Accounts"

                    Where

                      "Account Status" <> 'Closed'

                    and

                      "EOD Code" < '1';

                     

                     

                    concatenate (TB)

                    LOAD

                      "Account No",

                        "Product Category Name",

                        "Account Status",

                        "Branch Name",

                        "EOD Code",

                         //"Primary Customer - ID",

                        "Secondary Customer - ID" as [Customer-ID]

                       resident TB;

                     

                    drop field [Secondary Customer - ID];

                • Re: Two Similar Fields needed to be one
                  mark pratt

                  I have that working, but kind of new to the grouping function. I want to keep 'Yes' if any in the if for customer comes back as 'Yes'. This is what I have but getting error:

                   

                  LIB CONNECT TO 'CBS-SQL(Mark)';

                  Load

                    "Customer-ID",

                      "Branch Name",   

                      Max(if("Product Category Name"='DDA Accounts','Yes','No')) as "HasDDA",

                      Max(if("Product Category Name"='Loans','Yes','No')) as "HasLAS",

                      Max(if("Product Category Name"='CDs','Yes','No')) as "HasCOD",

                      Max(if("Product Category Name"='Savings Account','Yes','No')) as "HasSAV",

                      Max(if("Product Category Name"='Internet Bank','Yes','No')) as "HasIB"

                     

                  Group by [Customer-ID];

                     

                  TB:

                  LOAD

                    "Account No",

                      "Product Category Name",

                      "Account Status",

                      "Branch Name",

                      "EOD Code",

                      "Primary Customer - ID" as [Customer-ID],

                      "Secondary Customer - ID";

                  SQL SELECT

                    "Account No",

                      "Product Category Name",

                      "Account Status",

                      "Branch Name",

                      "EOD Code",

                      "Primary Customer - ID",

                      "Secondary Customer - ID"

                  FROM

                  Test.dbo."Accounts - All"

                  Where

                    "Account Status" <> 'Closed'

                  and

                    "EOD Code" < '1';

                   

                   

                  concatenate (TB)

                  LOAD

                    "Account No",

                      "Product Category Name",

                      "Account Status",

                      "Branch Name",

                      "EOD Code",

                      "Secondary Customer - ID" as [Customer-ID]

                  resident TB;

                   

                  drop field [Secondary Customer - ID];

                    • Re: Two Similar Fields needed to be one
                      Massimo Grossi

                      I would add the group by at the end of the script, something as (replace a, b, c with your values)

                       

                      Left join (S)

                      LOAD

                           "Customer-ID",

                           "Branch Name",  

                           Max(match([Product Category Name], 'a')) as HasA,

                           Max(match([Product Category Name], 'b')) as HasB,

                           Max(match([Product Category Name], 'c')) as HasC

                      Resident S

                      group by

                            "Customer-ID",

                            "Branch Name"

                           ;

                       

                       

                      Also remember to group by all the non aggregated fields; you did a mix, 2 fields in the load, 1 in the group by