Circular Link Issue

[#1]

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

[vikasmahajan]

create some sample data and attach your qvw and source.

Viikas

[#3]

Hi,

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

[effinty2112]

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
			4/1/2014	3000	9
		AC	3/1/2014	2000	9
			4/1/2014	500	11
		Total		6500	36
	PUNE	AY	3/1/2014	3000	13
			4/1/2014	1000	6
		Total		4000	19
NEW DELHI	DELHI	AB	3/1/2014	1000	4
			4/1/2014	500	6
		AX	3/1/2014	1500	7
			4/1/2014	1500	9
		AZ	4/1/2014	3000	11
		Total		7500	37
Total				18000	46

The data model:

[#5]

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

[#6]

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

[effinty2112]

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
			4/1/2014	3000	6
		1003	3/1/2014	2000	5
			4/1/2014	500	6
	PUNE	1002	3/1/2014	2500	3
			4/1/2014	1000	4
		1003	3/1/2014	500	0
NEW DELHI	DELHI	1001	3/1/2014	1500	2
			4/1/2014	1500	3
		1002	3/1/2014	1000	1
			4/1/2014	500	2
		1003	4/1/2014	3000	5
Total				18000	42

[#8]

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) ?

[#9]

Hi Andrew,

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