Group By

qlikuser2021 — Thu, 03 Jun 2021 21:12:10 GMT

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.

Re: Group By

MayilVahanan — Fri, 04 Jun 2021 01:09:37 GMT

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]

topic Group By in QlikView

Group By

Re: Group By