topic Re: Offset addition (Straight table) in QlikView

Offset addition (Straight table)

Tue, 30 Sep 2014 18:28:31 GMT

Hi I have below requirement

Current View

Role	Sources	Total
Role1	SRC1	100
	SRC2	123
Role2	SRC3	456
	SRC4	-546
Role2	SRC5	124
	SRC6	-456
	SRC7	-132

Please find below offset logic whihc needs to added to totals above

Offset Logic

Source	Offset
SRC6	SRC2
SRC7	SRC2
SRC4	SRC3

Final View should be as below

Role	Sources	Total
Role1	SRC1	100
	SRC2	711
Role2	SRC3	1002
	SRC4	-546
Role2	SRC5	124
	SRC6	-456
	SRC7	-132

SRC2=123-(-456)-(-132)

SRC3=456-(-546)

Please suggest how to implement this at UI level or Script level

Thnaks in advance

Re: Offset addition (Straight table)

JonnyPoole — Tue, 30 Sep 2014 18:40:01 GMT

I used this script to create a lookup table that you would manually populate and adjust for each offset.

The offset contains the multiplier '-1' and maps in the offsets. The chart expression is just:

sum( [Total] * multiplier)

Sources:

LOAD Role,

Sources,

Total

FROM

(ooxml, embedded labels, table is Sheet1);

//map all sources to themselves

MappingSources:

Load

Sources,

Sources as MappingSource,

1 as Multiplier

Resident Sources;

//Add additional offsets manually

Concatenate (MappingSources)

LOAD * INLINE [

Sources, MappingSource, Multiplier

SR6, SR2, -1

SR7, SR2, -1

SR8, SR3, -1

];

Re: Offset addition (Straight table)

Wed, 01 Oct 2014 02:11:26 GMT

Hi Jonathan,

I have created mapping table based on your suggestion.

But total is not linking correctly for the new MappingSource column. Total value is same for all the sources

Role	Sources	Total
Role1	SRC1	100
	SRC2	123
	SRC6	123
	SRC7	123
Role2	SRC3	456
	SRC4	456
Role2	SRC5	124
	SRC6	-456
	SRC7	-132

I'm looking for following output

Role	Sources	Total
Role1	SRC1	100
	SRC2	711
Role2	SRC3	1002
	SRC4	-546
Role2	SRC5	124
	SRC6	-456
	SRC7	-132

Please suggest. Thanks in advance

Re: Offset addition (Straight table)

JonnyPoole — Wed, 01 Oct 2014 03:11:55 GMT

are you using the qvw i provided ?