topic Re: duplicate records in QlikView

duplicate records

Anonymous — Mon, 28 Sep 2015 12:57:57 GMT

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

Re: duplicate records

jonathandienst — Mon, 28 Sep 2015 13:03:49 GMT

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.

Re: duplicate records

jonathandienst — Mon, 28 Sep 2015 13:12:19 GMT

>>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.

Re: duplicate records

avinashelite — Mon, 28 Sep 2015 13:22:52 GMT

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

Re: duplicate records

sasiparupudi1 — Mon, 28 Sep 2015 14:17:18 GMT

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

Re: duplicate records

jonathandienst — Mon, 28 Sep 2015 14:35:24 GMT

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 ...

Re: duplicate records

sasiparupudi1 — Mon, 28 Sep 2015 18:16:36 GMT

Hi Jonathan,

Thank you for pointing it out, point taken.

Sasi

Re: duplicate records

sasiparupudi1 — Mon, 28 Sep 2015 18:21:52 GMT

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

Re: duplicate records

Anonymous — Mon, 28 Sep 2015 23:14:47 GMT

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?

Re: duplicate records

evan_kurowski — Tue, 29 Sep 2015 03:47:03 GMT

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.