Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loops in Data

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.

3 Replies
hic
Former Employee
Former Employee

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

Not applicable
Author

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 EntityCountryCost CentreActual Financial (EUR)Calculated Financial (EUR)Variance
UK1United KingdomUKA11282.529.5
UK2United KingdomUKB56102.5-46.5
NL1NetherlandsNLC493514
US1United StatesUSA846519
US2United StatesUSB4965-16
Cost Centre Source Financial (EUR)
Cost CentreFinancial (EUR)
UKA100
UKB50
NLC50
USA100
USB50
Process 1 To:
Cost CentreRateLegal EntityCountryCalculated Output
UKA50%UK1United Kingdom50
UKA50%UK2United Kingdom50
Process 2 To:
Cost CentreRateLegal EntityCountryCalculated Output
UKB30%UK1United Kingdom15
UKB70%UK2United Kingdom35
NLC70%NL1Netherlands35
NLC15%UK1United Kingdom7.5
NLC15%UK2United Kingdom7.5
Process 3 To:
Cost CentreRateLegal EntityCountryCalculated Output
USA45%US1United States45
USA45%US2United States45
USA10%UK1United Kingdom10
Process 4 To:
Cost CentreRateLegal EntityCountryCalculated Output
USB40%US1United States20
USB40%US2United States20
USB20%UK2United Kingdom10
hic
Former Employee
Former Employee

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