topic Re: Loops in Data in QlikView

Loops in Data

Tue, 10 Jun 2014 09:11:43 GMT

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.

Re: Loops in Data

hic — Tue, 10 Jun 2014 10:31:49 GMT

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.

Re: Loops in Data

Tue, 10 Jun 2014 13:40:21 GMT

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

Re: Loops in Data

hic — Tue, 10 Jun 2014 22:38:54 GMT

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