duplicate records

[Anonymous]

I am getting duplicate records for "SUP#,"due to the join between TrainingContracts and ReturnContractTraining Contracts tables.  The "TrainingContractReturn" table only has one field that I can use to link to, so I need to find another way to remove duplicates other than with joins.

I am trying to use the Distinct (Group) functionality (as shown below but QlikView doesn't seem to like aliases anywhere in the select statement.  i.e. SQL didn't seem to mind when I was testing.   How else can i handle this issue in QlikView? Any suggestions appreciated.

Facts:

LOAD
FieldOfficeID as %ConsultantKey,
bdl_referred_by_id,
at_fault,
1 as SUP#,
if(isnull([bdl_referred_by_id]), 0, 1) as SUPBDLLead#,
if([BDL Referred By]='Not BDL Lead',1,0) as SUPNotBDLLead#,
If(signup_status_id=5,1,0) as SUPComplete#,

if(isnull(at_fault),0, 1) as SUPReturn#,
if(at_fault,1,0) as AtFaultReturn#,
if(at_fault,0,1) as NotAtFaultReturn#
;

SQL SELECT distinct (tc.id),
FieldOfficer.id as "FieldOfficeID",
FieldOfficer.team_id as "ConsultantTeam",
FieldOfficer.first_name ||' '|| FieldOfficer.surname as "FieldOfficer",
AccountManager.first_name ||' '|| AccountManager.surname as "Account Manager",
BDLRefferedBy.first_name ||' '|| BDLRefferedBy.surname as "BDL Referred By",
tc.bdl_referred_by_id,
last_kpi_action_date as "Status Date of change",
employer_id as "Employer ID",
BDLRefferedBy.id as BDLRefByID,
tc.id as TrainContractId,
tc.received_at ,
tc.account_manager_id,
at_fault,


from training_contracts tc
left join signup_statuses ss on tc.signup_status_id = ss.id
left join users FieldOfficer on FieldOfficer.id = tc.field_officer_id
Left join users AccountManager on AccountManager.id = tc.account_manager_id
left join users BDLRefferedBy on BDLRefferedBy.id = tc.bdl_referred_by_id
left join training_contract_returns tcr on tc.id = tcr.training_contract_id
group by tc.id,
FieldOfficer.id,
FieldOfficer.team_id,
FieldOfficer.first_name ||' '|| FieldOfficer.surname ,
AccountManager.first_name ||' '|| AccountManager.surname,
BDLRefferedBy.first_name ||' '|| BDLRefferedBy.surname ,
tc.bdl_referred_by_id,
BDLRefferedBy.id,
tc.id,
tc.finalised_on ,
tc.approved_on ,
tc.file_no,
tc.employee_id,
at_fault,
FieldOfficer.role;

Regards

Laura

[jonathandienst]

Everything after SQL is executed by the DB server, not by Qlikview, so this is more of a SQL question than a QV question.

>>I am trying to use the Distinct (Group) functionality (as shown below but QlikView doesn't seem to like aliases anywhere in the select statement.  i.e. SQL didn't seem to mind when I was testing

QV does not care about anything after the SQL statement and it is not executed by QV, so it cannot 'dislike' aliases in the SELECT statement. You might want to clarify this statement a little. The only parsing of the SELECT is to find the end of the statement to know what to send to the OLEDB/ODBC connection.

[jonathandienst]

>>SELECT distinct (tc.id),

What purpose do you expect the parenthesis to do here? AFAIK the DISTINCT applies to he entire row, not to a specific field...

>>If(signup_status_id=5,1,0) as SUPComplete#,

The field signup_status_id is not included in the Select, so it is not included in the recordset and the LOAD statement will fail.

[avinashelite]

Hi Laura,

As jontydkpi‌ mentioned anything after the SQL SELECT is completely the SQL DB query and not related to Qlikview.

From the LOAD till SQL SELECT its completely belongs to Qlikview scripting and Qlikview function can be used here not in select statement.

Please check your SQL query once and let us know if you face any issues 

[sasiparupudi1]

Hi

I think Its not the aliases that are your problem but lacking the use of aggregation functions like count,sum etc in your sql query. You are grouping the results but not telling the DB what aggregation it must perform on the data.

hth

Sasi

[jonathandienst]

That's not usually a  requirement. A SQL group by without an aggregation (must be on all fields in the select), is an alias for SELECT Distinct ...

[sasiparupudi1]

Hi Jonathan,

Thank you for pointing it out, point taken.

Sasi

[sasiparupudi1]

Hi Laura,

May be try a resident load and see if it helps?

Temp:

SQL SELECT distinct (tc.id),
FieldOfficer.id as "FieldOfficeID",
FieldOfficer.team_id as "ConsultantTeam",
FieldOfficer.first_name ||' '|| FieldOfficer.surname as "FieldOfficer",
AccountManager.first_name ||' '|| AccountManager.surname as "Account Manager",
BDLRefferedBy.first_name ||' '|| BDLRefferedBy.surname as "BDL Referred By",
tc.bdl_referred_by_id,
last_kpi_action_date as "Status Date of change",
employer_id as "Employer ID",
BDLRefferedBy.id as BDLRefByID,
tc.id as TrainContractId,
tc.received_at ,
tc.account_manager_id,
at_fault,


from training_contracts tc
left join signup_statuses ss on tc.signup_status_id = ss.id
left join users FieldOfficer on FieldOfficer.id = tc.field_officer_id
Left join users AccountManager on AccountManager.id = tc.account_manager_id
left join users BDLRefferedBy on BDLRefferedBy.id = tc.bdl_referred_by_id
left join training_contract_returns tcr on tc.id = tcr.training_contract_id
group by tc.id,
FieldOfficer.id,
FieldOfficer.team_id,
FieldOfficer.first_name ||' '|| FieldOfficer.surname ,
AccountManager.first_name ||' '|| AccountManager.surname,
BDLRefferedBy.first_name ||' '|| BDLRefferedBy.surname ,
tc.bdl_referred_by_id,
BDLRefferedBy.id,
tc.id,
tc.finalised_on ,
tc.approved_on ,
tc.file_no,
tc.employee_id,
at_fault,
FieldOfficer.role

;

Facts:

noconcatenate LOAD
FieldOfficeID as %ConsultantKey,
bdl_referred_by_id,
at_fault,
1 as SUP#,
if(isnull([bdl_referred_by_id]), 0, 1) as SUPBDLLead#,
if([BDL Referred By]='Not BDL Lead',1,0) as SUPNotBDLLead#,
If(signup_status_id=5,1,0) as SUPComplete#,

if(isnull(at_fault),0, 1) as SUPReturn#,
if(at_fault,1,0) as AtFaultReturn#,
if(at_fault,0,1) as NotAtFaultReturn#
resident Temp ;

drop table Temp;

hth

Sasi

[Anonymous]

Hi All,

i fell asleep !!

Jonathan the field signup_status_id is included in the Select but i removed a whole lot of field (accidently including that one) not to load too much into the screenshot.

Sasi,  I tried what you have selected with no luck.  i dont quite understand the first section though.  Shouldn't there be a load for the first part?

[evan_kurowski]

Hello Laura,

I agree with Jonathan in that it may not be enough to have [signup_status_id] be a part of the SQL join syntax, it must be returned as a result column in the SQL statement for it to be passed along to the QV preceding load.

As for the distinction issues, this could be because the SQL statement "intends" to return a 15-field result set, but is only passing on 5 of those fields to QlikView.  If you drop 10 columns from a 15-column table, a lot of distinction might be lost.

Maybe throw a RecNo() as RECORD_ID to force QlikView side distinction.  Test the rowcount on the SQL by itself, then attach the QlikView preceding load and verify before/after rowcounts.