topic Re: Data issue - doubling records in QlikView

Data issue - doubling records

zagzebski — Thu, 05 Mar 2015 02:47:35 GMT

I can't figure out this problem. I have a BegBal table then transform it to to a BegBal2 table. I don't understand but it is doubling up the records.

Agency	Amount Beg Bal
TLS	7465.74
M3I	164288.34

Key\|Agency1	Amount Beg Bal
M3I	7465.74
M3I	164288.34
TLS	7465.74
TLS	164288.34

BegBal:

LOAD
Agency,
[Amount Beg Bal]
FROM
[..\SourceDocuments\QVD\Layer2\BegBal.qvd]
(qvd);

BegBal2:
Load
Agency as Key|Agency1,
Resident BegBal;

Re: Data issue - doubling records

JonnyPoole — Thu, 05 Mar 2015 03:04:35 GMT

it autoconcatenating the tables together into Begbal table. Begbal2 is never created

add 'noconcatenate' before the 2nd table.

But do you still want the 1st ? it will create an ugly link table between the 2 if you don't drop the first. fyi.

BegBal:

LOAD
Agency,
[Amount Beg Bal]
FROM
[..\SourceDocuments\QVD\Layer2\BegBal.qvd]
(qvd);

noconcatenate
BegBal2:
Load
Agency as Key|Agency1,
Resident BegBal;

Re: Data issue - doubling records

MayilVahanan — Thu, 05 Mar 2015 04:20:58 GMT

What you are trying to achieve? Could you please explain the scenario?

From your data model, its creating "Cartesian Product". There is no link between BegBal and BegBal2..

In result, it produce the wrong result.

Re: Data issue - doubling records

PrashantSangle — Thu, 05 Mar 2015 04:29:20 GMT

Hi,

I dont think so that nonconcatenate will solve his issue.

Since both table BegBal and BegBal2 are independent table

but when you take this in table box, it will take all possible combination of

Key|Agency1 and [Amount Beg Bal] therefore value is duplicating.

After reloading your script check in table viewer there are two independent table there is no relation between these two table therefore you can see these kind of behaviour.

Create relation between them or create key in main table no need to use resident load.

and there is error in your script you have inserted , after Key|Agency1 just check it.

I hope you understand

Regards

Re: Data issue - doubling records

renjithpl — Thu, 05 Mar 2015 04:44:23 GMT

Hi Zagzebski,

I think the script is wrong may be you are missing something,

it sshould be like this

LOAD

Agency,
[Amount Beg Bal]
FROM
[..\SourceDocuments\QVD\Layer2\BegBal.qvd]
(qvd);

BegBal2:
Load
Agency as Key|Agency1,
[Amount Beg Bal] as [Amount Beg Bal_New]
Resident BegBal;

and in the front end create a chart,

bring dimension - Key|Agency1 and
Expression - sum(Amount Beg Bal_New)

You can rename your expression as you want.

Re: Data issue - doubling records

jagan — Thu, 05 Mar 2015 09:07:03 GMT

Hi,

Not sure what you are trying to do, but try like this

BegBal:

LOAD
Agency,
[Amount Beg Bal]
FROM
[..\SourceDocuments\QVD\Layer2\BegBal.qvd]
(qvd);

BegBal2:
Load DISTINCT

Agency,
Agency as Key|Agency1,
Resident BegBal;

Hope this helps you.

Regards,

Jagan.

Re: Data issue - doubling records

simenkg — Thu, 05 Mar 2015 09:13:42 GMT

There is no link between your two tables. Key|Agency1 comes form one table and [Amount Beg Ball] comes from the other.

Putting these two fields in a table box will create a Cartesian product between them creating all possible combinations.

Re: Data issue - doubling records

JonnyPoole — Thu, 05 Mar 2015 13:39:30 GMT

Agreed ... ignore above post as inaccurate. amazing what a good night sleep can do for your ability to read carefully.