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

Offset addition (Straight table)

Hi I have below requirement

Current View

RoleSourcesTotal
Role1SRC1100
SRC2123
Role2SRC3456
SRC4-546
Role2SRC5124
SRC6-456
SRC7-132

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

Offset Logic

SourceOffset
SRC6SRC2
SRC7SRC2
SRC4SRC3

Final View should be as below

RoleSourcesTotal
Role1SRC1100
SRC2711
Role2SRC31002
SRC4-546
Role2SRC5124
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

3 Replies
JonnyPoole
Employee
Employee

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)

Capture.PNG.png

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

];

Not applicable
Author

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
SourcesTotal
Role1SRC1100
SRC2123
SRC6123
SRC7123
Role2SRC3456
SRC4456
Role2SRC5124
SRC6-456
SRC7

-132 

I'm looking for following output

RoleSourcesTotal
Role1SRC1100
SRC2711
Role2SRC31002
SRC4-546
Role2SRC5124
SRC6-456
SRC7-132

Please suggest. Thanks in advance

JonnyPoole
Employee
Employee

are you using the qvw  i provided  ?