create "intelligent" join table , but not on all tables ...

[zarmoton]

Hello,

Help ! I have issues with the different type of join on differents table:

It's seems simple, but impossible for me to find the solution:

I just want to retrieve couples of information just from the referential table, and don't want to have some link on data table

I have 2 tables "referentials", and 2 tables of data like that

The 2 tables of referentials

Refsales

idsales	Name	Firstname
1	Gates	Bill
2	Dupont	Paul
3	Obama	Barak

Refclients

idclient	clientName	Country	idsales
A	Porsche	FR	1
B	Renault	US	1
C	Ford	FR	2

and 2 tables tables of data:

Revenues:

idsales	idclient	Amount Revenues
1	A	10
2	B	3

Expenses

idsales	idclient	AmountExp
1	B	5
2	C	20
2	A	30

What i would like to see is:

3 Tables or pivot table:
REVENUES
idsales	Firstname	Name	idclient	clientName	Country	Amount Revenues
1	Bill	Gates	A	Porsche	FR	10
2	Paul	Dupont	B	Renault	US	3
EXPENSES
idsales	Firstname	Name	idclient	clientName	Country	Amount Expenses
1	Bill	Gates	B	Renault	US	5
2	Paul	Dupont	A	Porsche	FR	30
2	Paul	Dupont	C	Ford	FR	20
REFERENTIAL CLIENTS
idclient	clientName	Country	idsales	Firstname	Name
A	Porsche	FR	1	Bill	Gates
B	Renault	US	1	Paul	Dupont
C	Ford	FR	2	Paul	Dupont

attach is the excel file for the input data, and what i have try to do it in QV.

Thank you for your help

[kaushiknsolanki]

Hi,

     Have a look at the attachment.

Regards,

Kaushik Solanki

[zarmoton]

Hi,

thank you for your help, but it's ok for the first two table (revenue and exepnses)

and not ok for referential clients because in your table now there is 2 rows of id client client A with 2 names, which is wrong.

it should be only:

REFERENTIAL CLIENTS
idclient	clientName	Country	idsales	Firstname	Name
A	Porsche	FR	1	Bill	Gates
B	Renault	US	1	Paul	Dupont
C	Ford	FR	2	Paul	Dupont

thk you Kaushik

[kaushiknsolanki]

Hi,

     I guess that is the same table as your expense table except the expense amount and the sequence of the table.

     You can do this and you will get what you want.

     Copy the expense table and then go to presentation and click on Expense Amount Expression and Click on Hide.

Regards,

Kaushik Solanki

[zarmoton]

sorry, but i don't really understand your answer,

can you post the qvw as example

Clearly, i have done a simple example here, but my idea is not to merge the table

i'm looking for an example of how to retrieve information coming from referential, when i have a lot of data tables

thk yu

[kaushiknsolanki]

Find the attachment.

Regards,

Kaushik Solanki

[zarmoton]

Hello,

in fact, not really good

why ?

In the table data: Expenses and Revenues: idsales is the sales who have done the revenues or the expenses

But in Referential clients: id sales is the sales in charge of the relation with the client.

So in your example, you have retrieve on the referential client , the id of the sales with name/firstname coming from the tables expenses. which is not good.

Have a look in details on my first post, of the exact expected result that i'm looking for.

Regards,