Using Table box

[#1]

Hi,

I have a problem when I using a table box.

I have two tables, Projects and Invoices.

Projects: ProjectCode, ProjectDescription

Invoices: InvoiceID, Year, Month, Amount, ProjectCode.

These tables are stored and then read from QVDs. After the Invoices loading I use a LOAD to sum the Amount:

InvoicesAmount: LOAD sum(Amount) as SumAmount resident Invoices where DocumentType = '1' group by Year, Month, ProjectCode.

When I put ProjectCode, Year, Month and SumAmount on a Table box, I can see duplicate values for SumAmount also for month without any invoice amount.

Any suggests to me, please? Thanks

[pat_agen · Accepted solution]

hi,

I think your first problem is using a table box. You would be ebtter off with a straight table and use a default expression like =1 to display all lines.

that aside I think also you will have issue joining your tables (unless you haven't put all your code in your first post).

If you have create a 3rd table calles InvoicesAmount with the staement you have posted this will simply create a single column table. that field is called SumAmount and is not linked to the other two tables.

If then in a table box you wish to display fields comng from Project, Invoices and this it will display SumAmount against every record it find from the first two tables.

[Hit ctrl T in your qvw document to see how your tables are linked together].

What is the purpose of thsi SumAmount field? Why do you want/need to create this in the script and not simply in a chart when you need it?

Answering these two questions will help define how how and when this calculation shoud be done.

[#3]

Oh thanks. You are right.

When I calculate the InvoicesAmount I must add also the grouping fields.

Thanks, I've solved the issue.

[#4]

Hey,

Can you share either your data/qvw for the same,it will help full for us.

Thanks & Regards

Jai