Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Employee
Employee

Re: Offset addition (Straight table)

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

Re: Offset addition (Straight table)

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

Employee
Employee

Re: Offset addition (Straight table)

are you using the qvw  i provided  ?

Community Browser