How to Join these Tables?

[psk180590]

Hello All,

I have data in the following format in different tables

Table1:

CID    Date    Region    Area    VisitNum    QNum    ANum    ProductFamily    ArtikelName

Table2:

Region    Area    TotalVisitsArea

Table3:

Region    TotalVisitsRegion

Table4:

TotalVisitsCountry

Basically, i need to generate one final table in the below format:

CID   Date   Region   Area   VisitNum   QNum   ANum   ProductFamily   ArtikelName     TotalVisitsArea     TotalVisitsRegion  TotalVisitsCountry

I tried doing a LeftJoin but, the TotalVisits respond only for Region and Area, but i want them to also respond upon selection of other fields say VisitNum or QNum or ANum etc.

Can you please suggest me how to achieve this.

Thanks
Sai.

[avinashelite]

share the sample data that will help us suggest ...based on your inputs left join should work with the composite key

Table1:

Region&'-'&Area as AR_Key

left join (AR_Key)

table2

Region&'-'&Area as AR_Key,

TotalVisitsArea

left join (Region)

table 3 :

[Colin-Albert]

You can also do this without joining the tables by using a Mapping table and the Applymap function.

Don't join - use Applymap instead

[prieper]

Table1: LOAD * RESIDENT Table1;

LEFT JOIN (Table1) LOAD Area, TotalVisitsArea RESIDENT Table2;     // guess that Region is redundant

LEFT JOIN (Table2) LOAD Region, TotalVisitsRegion RESIDENT Table3; // is this required, or is it total for all Area in a Region?

should do (or replace the RESIDENT with the datasource).

TotalVisitsCountry does no seem to have a key in common?

Do the TotalVisitsArea total up to TotalVisitsRegion?

What relevance should the Date-field have?

[psk180590]

Hello All,

Thanks for the quick suggestions.

Here is my sample data attached. I have total of 3 Sheets

• MasterSheet
• Visits per Area(TotalVisitsArea)
• Visits per Region(TotalVisitsRegion)
• Sum of Visits per Region is the TotalVisitsCountry

Regards

Sai.

[avinashelite]

As per your data set the logic which I suggested should work....and you don't what your excepting as the results ?

according to QNum you need the visits ?? then probably you need to count he visits in the master table

[psk180590]

Hi Avinash,

I would like the NumberofVisits to be dynamic.

Say, i want to make a BarChart which displays the NumberOfVisits for a particular QNo month wise.

i.e., TotalVisitsArea    vs TotalVisitsRegion vs TotalVisitsCountry based on Qno and Month as selectable dimension.

[avinashelite]

then count the number of CID or VisitID that should do I guess

[psk180590]

Hello Avinash,

Region	Area	Monat	CID	VisitID	CVID	ProdID	QNo	ANo
1000	1001	Feb	4294972607	1	42949726071	4094	560	20
1000	1001	Feb	4294972607	1	42949726071	4095	570	20
1000	1001	Feb	4294972607	1	42949726071	4103	540	30
1000	1001	Feb	4294972607	1	42949726071	44173	150	10
1000	1001	Feb	4294972607	1	42949726071	50342	140	20
1000	1001	Apr	4294972607	2	42949726072	4094	560	30
1000	1001	Apr	4294972607	2	42949726072	4095	570	30
1000	1001	Apr	4294972607	2	42949726072	4103	540	30
1000	1001	Apr	4294972607	2	42949726072	44173	150	10

Thanks for the suggestions, i'm closest to achieving my result with the help of composite key. But, there are still problems haunting me.

As in the above example data you can see for the the same CID there are Two Visit IDs and the Column CVID is a combination of both.

But, there are 5 ProdIDs in Visit 1 and 4 in Visit 2.

Now, what i would like to achieve is, a single column say(NumberofCVID) which gives me the Count of CVID Per Area, QNo, ANo, ProdID.

Here, if i select the Area the Column should give me 2

If i select ProdID 4094 result should be 2

If i select ProdID 50342 result should be 1

Same way with QNo if i select 560 it should be 2 and if 50342 is selected it should be 1

Also, with ANo i will have a text box where QNO=560 and ANo=20 then it should be 1 .

I have attached an updated sample data sheet. Can you please check if you need more detailed view.

Regards

Sai.