Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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?
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
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';
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];
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];
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