Group By

[qlikuser2021]

I have a qvd that has duplicate Bframeid 's (account number)  which is causing my total to be 2 records higher than it should be.  I am trying to use a group by statement to remove the duplicate Bframeid's but doing this results in my total number of Bframeid's to be almost twice as high.

Below is what I had where I was getting a couple extra Bframeid's because the qvd had duplicate Bframeid's.

left join (distinct_soli)

FDR:
LOAD
[Bframeid] as [ACCT_ID],
[Bframeid] as [ACCT_ID_FDR],
[OPENDATE]
FROM [..\..\..\QVD\QVD_FDR.qvd](qvd);

Using the group by below to remove duplicate Bframeid's  caused my number of accounts to almost double. 

left join (distinct_soli)

FDR:
LOAD
[Bframeid] as [ACCT_ID],
[Bframeid] as [ACCT_ID_FDR],
min([OPENDATE]) as [OPENDATE]

FROM [..\..\..\QVD\QVD_FDR.qvd](qvd)
GROUP BY [Bframeid] ;

Any ideas why this is happening? I don't understand how removing duplicate Bframeid's would increase my total.

[MayilVahanan · Accepted solution]

HI @qlikuser2021 

Do you have multiple Open Date for single Bframeid?

Instead of Left join, you can keep the table separately by link with ACCT_ID.

And also, below statement, will give distinct acct_id with minimum open date.. might be, you are using some other calculation which makes double the values.

LOAD
[Bframeid] as [ACCT_ID],
[Bframeid] as [ACCT_ID_FDR],
min([OPENDATE]) as [OPENDATE]

FROM [..\..\..\QVD\QVD_FDR.qvd](qvd)
GROUP BY [Bframeid]