Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
psk180590
Creator III
Creator III

How to Join these Tables?

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.

8 Replies
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
Master II
Master II

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hello Avinash,

        

RegionAreaMonatCIDVisitIDCVIDProdIDQNoANo
10001001Feb4294972607142949726071409456020
10001001Feb4294972607142949726071409557020
10001001Feb4294972607142949726071410354030
10001001Feb42949726071429497260714417315010
10001001Feb42949726071429497260715034214020
10001001Apr4294972607242949726072409456030
10001001Apr4294972607242949726072409557030
10001001Apr4294972607242949726072410354030
10001001Apr42949726072429497260724417315010

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.