Loops in Data

[#1]

Hi All – a self taught Qlikview wannabe here struggling with data that creates loops – any advice would be well received!

I have several sets of financial data which I am trying to compare to give me comfort that the output of a process is what I’d reasonably expect it to be.

The actual data consists of a set of financial data including cost centre, legal entity and country. The calculated source data includes the same but is then separated appropriately, put through 4 separate processes and aggregated to give the final result – the comparison between actual and calculated can then be made by legal entity and country.

The problem is that when I try to aggregate and link the calculated data by more than one dimension to the actual data I get loops.

Has anyone got any ideas about the best way to either:

a)      Model the data appropriately or

b)      What functions would be best suited to help me aggregate the data?

Many thanks.

[hic]

Without seeing your data, it is difficult to give a definite answer, but my guess is that you should concatenate the actual numbers with the calculated, so you get them in one table.

See also

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

HIC

[#3]

Hi Henric,

Thanks for your response.

I am unable to load a file due to my company firewall but the data would look something like this:

Input:				Output:
Legal Entity	Country	Cost Centre	Actual Financial (EUR)	Calculated Financial (EUR)	Variance
UK1	United Kingdom	UKA	112	82.5	29.5
UK2	United Kingdom	UKB	56	102.5	-46.5
NL1	Netherlands	NLC	49	35	14
US1	United States	USA	84	65	19
US2	United States	USB	49	65	-16
Cost Centre Source Financial (EUR)
Cost Centre	Financial (EUR)
UKA	100
UKB	50
NLC	50
USA	100
USB	50
Process 1		To:
Cost Centre	Rate	Legal Entity	Country	Calculated Output
UKA	50%	UK1	United Kingdom	50
UKA	50%	UK2	United Kingdom	50
Process 2		To:
Cost Centre	Rate	Legal Entity	Country	Calculated Output
UKB	30%	UK1	United Kingdom	15
UKB	70%	UK2	United Kingdom	35
NLC	70%	NL1	Netherlands	35
NLC	15%	UK1	United Kingdom	7.5
NLC	15%	UK2	United Kingdom	7.5
Process 3		To:
Cost Centre	Rate	Legal Entity	Country	Calculated Output
USA	45%	US1	United States	45
USA	45%	US2	United States	45
USA	10%	UK1	United Kingdom	10
Process 4		To:
Cost Centre	Rate	Legal Entity	Country	Calculated Output
USB	40%	US1	United States	20
USB	40%	US2	United States	20
USB	20%	UK2	United Kingdom	10

[hic]

First of all, you can put all process data in one table. E.g.

Load *, 'Process1' as Process From Process1;

Concatenate

Load *, 'Process2' as Process From Process2;

etc.

Secondly, you have cost centre, legal entity and country in two tables, so you might need to create a composite key from these:

     [Cost Centre] & '|' & [Legal Entity] & '|' & Country as Key

and just keep the separate fields in on of the tables: either the process table or the input table.

Then it ought to work.

HIC