topic Re: Circular Link Issue in QlikView

Circular Link Issue

Fri, 04 Dec 2015 09:12:02 GMT

Hi Team,

I am sharing you my sample table structure ( not actual but only sufficient information) to describe my problem and requirement.

The Yellow table is recently added into the application. which have YEAR-MONTH, MEMBERID, CITY wise Cumulative Growth.

due to CITY i am getting Circular Reference. but if i remove the Reference by changing the name i am not able to get link city.

how can i get below mention Information by SET Analysis or any other way.

Thanks in Advance

JKV

Re: Circular Link Issue

marcus_sommer — Fri, 04 Dec 2015 09:16:21 GMT

There are two possibilities. One is to merge these table with the other tables and the second is to create a bridge-table. I think this will help you: Get started with developing qlik datamodels

- Marcus

Re: Circular Link Issue

vikasmahajan — Fri, 04 Dec 2015 09:22:48 GMT

just remove city from cumulative client and check

Vikas

Re: Circular Link Issue

Fri, 04 Dec 2015 10:13:03 GMT

Hi.

Not possible then how can i get citywise information then will show MEM_MONTH_KEY wise only .

Re: Circular Link Issue

marcus_sommer — Fri, 04 Dec 2015 10:40:04 GMT

If I look a second time on your screenshot I think it would be worth to try to get your results without these cummulative table and simply sum and count within the charts.

- Marcus

Re: Circular Link Issue

Fri, 04 Dec 2015 10:50:41 GMT

Hi ,

Not possible, because i did not load whole Client Master (CLIENT TABLE) because its big in size. i load using Exisit() and only those who are present in TRADE TABLE. if i do then it will only count those are present in trade table the Client who register but did not trade that goes miss from here.

and second table Cumulative City Client i Keep Count separately .

if i load the full Client Master (CLIENT TABLE) then i can easily do.

Can this possible to compare two fields in set Analysis?

Re: Circular Link Issue

jonathandienst — Fri, 04 Dec 2015 11:00:39 GMT

Are you trying to count active clients (exist in trade table and have actually traded) or all clients (exist in client table and may not have traded)?

Re: Circular Link Issue

vikasmahajan — Fri, 04 Dec 2015 11:01:55 GMT

concatenate or use joins for both client tables

Re: Circular Link Issue

Fri, 04 Dec 2015 11:03:50 GMT

Hi,

you are right jonathan. i want to count all Clients, whether they traded or not traded did not matter.

Re: Circular Link Issue

Fri, 04 Dec 2015 11:06:21 GMT

Hi.

I think that is not good to do because both are different in dimension. Client is pure Master table where Cumulative Client is Fact Table.

also the cumulative value is Month city Member wise

Re: Circular Link Issue

vikasmahajan — Fri, 04 Dec 2015 11:09:01 GMT

create some sample data and attach your qvw and source.

Viikas

Re: Circular Link Issue

Fri, 04 Dec 2015 11:30:27 GMT

Hi,

Please find attached Excel with sample Data and QVW for same.

Re: Circular Link Issue

effinty2112 — Fri, 04 Dec 2015 11:55:50 GMT

Hi Jitendra,

Vikas' suggestion to remove the city field from the cumulative table looks good to me. You can get this table, is this not what you would expect?:

STATE	CITY	CLIENTID	YEAR-MONTH	sum(TRADEVALUE)	sum([CUMATIVE COUNT])
MAHARASHTRA	MUMBAI	AA	3/1/2014	1000	7
		AA	4/1/2014	3000	9
		AC	3/1/2014	2000	9
		AC	4/1/2014	500	11
		Total		6500	36
	PUNE	AY	3/1/2014	3000	13
		AY	4/1/2014	1000	6
		Total		4000	19
NEW DELHI	DELHI	AB	3/1/2014	1000	4
		AB	4/1/2014	500	6
		AX	3/1/2014	1500	7
		AX	4/1/2014	1500	9
		AZ	4/1/2014	3000	11
		Total		7500	37
Total				18000	46

The data model:

Re: Circular Link Issue

Fri, 04 Dec 2015 12:42:42 GMT

Hi,

Sorry to say that but what u have given is totally wrong and data is also not matching as per city wise etc. if you see closely on 3/1/2014 Memberid 1001 have 2 clients AA and AX and cumlative count is MUMBAI is 5 and DELHI is 2

sample output what i want as below:

SELECTION :

YEAR-MONTH :3/1/2014

MEMBERID : 1001

OUTPUT

CITY	TRADE VALUE	CUMULATIVE COUNT
MUMBAI	1000	5
DELHI	1500	2

SELECTION :

YEAR-MONTH :4/1/2014

MEMBERID : 1001

OUTPUT

CITY	TRADE VALUE	CUMULATIVE COUNT
MUMBAI	1000	6
DELHI	1500	3

Re: Circular Link Issue

Fri, 04 Dec 2015 13:02:15 GMT

The requirement is to show Monthly , Member, City wise Cumulative Client Growth. i have already processed the data and keep as it in the table Cumulative table.

MONTH_MEMBER_KEY	CITY	CUMATIVE COUNT
41699/1001	MUMBAI	5
41699/1001	DELHI	2
41730/1001	MUMBAI	6
41730/1001	DELHI	3

i can show this separate table easiy. but i have other table which show monthly, Member, City ( City comes from Client Master) wise Trade value.

Both individual separate table work fine.

i want to megre both table by city so i can see value side by side in same table.

hope now you understand my requirement.

( changed TBL_CUMULATIVE CITY field name to CUMULATIVE_CITY field name

SELECTION

YEAR-MONTH : 3/1/2014

MEMBERID : 1001

CITY	Sum(TRADEVALUE)
	2500
MUMBAI	1000
DELHI	1500

CUMULATIVE_CITY	Sum([CUMATIVE COUNT])
	7
DELHI	2
MUMBAI	5

Re: Circular Link Issue

effinty2112 — Fri, 04 Dec 2015 13:25:10 GMT

Left Join(TBL_TRADE)

Load * Resident TBL_CLIENT;

Left Join(TBL_TRADE)

Load * Resident TBL_CUMULATIVE;

DROP Tables TBL_CLIENT, TBL_CUMULATIVE;

STATE	CITY	MEMBERID	YEAR-MONTH	sum(TRADEVALUE)	sum([CUMATIVE COUNT])
MAHARASHTRA	MUMBAI	1001	3/1/2014	1000	5
		1001	4/1/2014	3000	6
		1003	3/1/2014	2000	5
		1003	4/1/2014	500	6
	PUNE	1002	3/1/2014	2500	3
		1002	4/1/2014	1000	4
		1003	3/1/2014	500	0
NEW DELHI	DELHI	1001	3/1/2014	1500	2
		1001	4/1/2014	1500	3
		1002	3/1/2014	1000	1
		1002	4/1/2014	500	2
		1003	4/1/2014	3000	5
Total				18000	42

Re: Circular Link Issue

Mon, 07 Dec 2015 04:24:45 GMT

Hi Andrew,

Thanks for your reply.

My original TBL_TRADE have around 23 columns and 3 Cr records and TBL_CLIENTS have around 27 Columns and around 32 lakh records.

My question is if i do join did it take more disk space on my system and also on memory (RAM) ?

Re: Circular Link Issue

Mon, 07 Dec 2015 06:54:19 GMT

Hi Andrew,

i have tried as you said. my Server get hanged. i think its happen due to not sufficient memory (RAM).