Two Similar Fields needed to be one

[mapratt82]

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

[maxgro]

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?

[mapratt82]

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

[Anonymous]

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';

[maxgro · Accepted solution]

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];

[mapratt82]

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];

[maxgro]

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