Data issue - doubling records

[zagzebski]

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;

[JonnyPoole]

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;

[MayilVahanan]

Hi

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. 

[PrashantSangle]

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

[renjithpl]

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.

[jagan]

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.

[simenkg]

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.

[JonnyPoole]

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