topic Re: How to Join these Tables? in QlikView

How to Join these Tables?

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to Join these Tables?

avinashelite — Tue, 05 Dec 2017 14:05:23 GMT

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 :

Re: How to Join these Tables?

Colin-Albert — Tue, 05 Dec 2017 14:09:08 GMT

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

Don't join - use Applymap instead

Re: How to Join these Tables?

prieper — Tue, 05 Dec 2017 14:15:37 GMT

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?

Re: How to Join these Tables?

psk180590 — Tue, 05 Dec 2017 14:28:06 GMT

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.

Re: How to Join these Tables?

avinashelite — Tue, 05 Dec 2017 14:40:49 GMT

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

Re: How to Join these Tables?

psk180590 — Tue, 05 Dec 2017 14:46:11 GMT

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.

Re: How to Join these Tables?

avinashelite — Tue, 05 Dec 2017 14:57:22 GMT

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

Re: How to Join these Tables?

psk180590 — Thu, 07 Dec 2017 09:39:12 GMT

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.