topic Re: if against Dynamic column with Sum in QlikView

if against Dynamic column with Sum

Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

First time posting and pretty new to Qlik, looking to see if anyone is able to assist with a query I have.

I'm looking to loop through a number of columns and where the a value is held sum the variance, loading script I've currently scripted is as below -

ColumnMap:

Mapping

LOAD * INLINE

[

IssueNo, Issue

1, IssueA

2, IssueB

3, IssueC

4, IssueD

];

let vNoRows = NoOfRows('ColumnMap');

For k = 1 to $(vNoRows)

LET V=peek('Issue',$(k),'ColumnMap');

QUALIFY *;

concatenate KI:

LOAD

$(V) AS Issue

,if ($(v) = '1',sum([Variance])) AS [Variance]

RESIDENT KI_tmp;

next k;

KI_tmp is represented as below -

Record	IssueA	IssueB	IssueC	Variance
1	0	0	1	-10
to	1	0	0	-20
10000	0	1	0	-30

I want KI to look like -

Issue	Variance
IssueA	-20
IssueB	-30
IssueC	-10
IssueD	0

Any help or advice much appreciated

Ricki

Re: if against Dynamic column with Sum

sunny_talwar — Fri, 03 Mar 2017 13:14:58 GMT

May be like this

KI_tmp:

CrossTable(Issue, Value, 2)

LOAD Record,

Variance,

IssueA,

IssueB,

IssueC,

IssueD;

LOAD * INLINE [

Record, IssueA, IssueB, IssueC, IssueD, Variance

1, 0, 0, 1, 0, -10

to, 1, 0, 0, 0, -20

10000, 0, 1, 0, 0, -30

];

KI:

LOAD Issue,

Variance

Resident KI_tmp

Where Value = 1;

DROP Table KI_tmp;

Concatenate(KI)

LOAD Issue,

0 as Variance;

LOAD * INLINE

[

IssueNo, Issue

1, IssueA

2, IssueB

3, IssueC

4, IssueD

] Where not Exists(Issue);

Re: if against Dynamic column with Sum

Fri, 03 Mar 2017 13:24:31 GMT

Hi,

There are circa 100,000 rows in KI_tmp.

Would this mean typing each row out in the format i want or have i misunderstood the solution?

Re: if against Dynamic column with Sum

sunny_talwar — Fri, 03 Mar 2017 16:26:21 GMT

I don't know what you mean? Can you try out the solution may be and see if it works for you or not?

Re: if against Dynamic column with Sum

Anil_Babu_Samineni — Fri, 03 Mar 2017 16:43:20 GMT

In fact, Rows doesn't matter over here. You may get as you expected output by using Sunny Code. That will indicate the issue too