Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mapratt82
New Contributor III

Two Similar Fields needed to be one

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Two Similar Fields needed to be one

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

6 Replies
MVP
MVP

Re: Two Similar Fields needed to be one

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
New Contributor III

Re: Two Similar Fields needed to be one

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

viveknair1234
New Contributor III

Re: Two Similar Fields needed to be one

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

MVP
MVP

Re: Two Similar Fields needed to be one

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
New Contributor III

Re: Two Similar Fields needed to be one

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

MVP
MVP

Re: Two Similar Fields needed to be one

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