if against Dynamic column with Sum

[#1]

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	IssueD	Variance
1	0	0	1	0	-10
to	1	0	0	0	-20
10000	0	1	0	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

[sunny_talwar]

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);

[#3]

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?

[sunny_talwar]

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

[Anil_Babu_Samineni]

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