Multiply Occurring Fields

[#1]

Hello Community,

I am running into an issue where the table I want to join to the "Fact table" has multiply occuring fields (acct #s in this case). This is causeing my calculations to be skewed (Sum (LOS), Sum(Total Charges) etc.) So for example my two tables would looks like this:

FACT_TABLE:

Acct #	Principal Diagnosis	Cases	Length of Stay	Total Charges
Z1	DX5	1	5 days	$10000
Z2	DX7	1	10 days	$20000
Z3	DX4	1	20 days	$40000
Z4	DX3	1	2 days	$5000

SEC_DIAG:

Acct #	Secondary Diagnosis
Z1	DX15
Z1	DX23
Z1	DX6
Z2	DX2
Z2	DX9

I would have orginally liked to do a join so that for Acct# Z1 you can see the Principal Diagnosis and the 3 related Secondary Diagnosis while Cases (1), LOS (5 days), and Total Charges ($10000) remain static. Obviously with a join I am going to see all these fields populated 3 times and therefore my Cases, LOS, and Total Charges become skewed.

Is there a work around for this?

Any help or suggestions is appreciated,

EK

[sushil353]

Why do u want join here.

Qlikview will automaticaly associate it and when u will select Z1 then u will be able to see the secondary as well...

[#3]

Right, even without saying join it will connect the two tables in that same fashion (excuse my use of join). it will look like this:

Acct#	Principal Diagnosis	Secondary Diagnosis	Cases	Length of Stay	Total Charges
Z1	DX5	DX15	1	5 days	$10,000
Z1	DX5	DX23	1	5 days	$10,000
Z1	DX5	DX6	1	5 days	$10,000

Then as I enter in my formulas Sum(LOS)=15 days. This is incorrect.

How I would like it to "see" the data is like this:

Acct#	Principal Diagnosis	Secondary Diagnosis	Cases	Length of Stay	Total Charges
Z1	DX5	DX15	1	5 days	$10,000
		DX23
		DX6

Then you can see wihat Secondary Diagnosis are associated with the account but it does not skew my financial fields.

[CELAMBARASAN · Accepted solution]

Hi,

Check the attached hope it helps

[#5]

Hello Celam,

The solution did work, however, the concatenated field looks poor and creates useless composite keys. Is there a way to clean this up so a list box would only show single codes?

Such as:

DX1

DX2

DX3

vs.

DX1,DX2,DX3

DX4,DX5

I understand this may be changing the solution entirely, however, this way I can create a crosstable that will make the correct associations. Right now it links everything together because of all the concatenated strings. 

EK