I have data in the following format in different tables
CID Date Region Area VisitNum QNum ANum ProductFamily ArtikelName
Region Area TotalVisitsArea
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.
share the sample data that will help us suggest ...based on your inputs left join should work with the composite key
Region&'-'&Area as AR_Key
left join (AR_Key)
Region&'-'&Area as AR_Key,
left join (Region)
table 3 :
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?
Thanks for the quick suggestions.
Here is my sample data attached. I have total of 3 Sheets
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
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.
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.