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

Create new field in script; new number in each row is based on a varying calculation with different result to each row

Appreciate if someone can help:

  - I have a table that I need to create some new fields in the script

  - the numbers (e.g. X1, X2, ...) in each row (e.g. row 1, 2, ...) from each new field (e.g. newfield26, newfield27, ...) has to be created base on a formula that takes reference to two sets of numbers,

  1.  numbers in the 'Points' field (e.g. P1, P2, ....), and

  2.  numbers in a particular field (e.g. M26, M27, ...) in another table

Below I have illustrated how the formula shall look like if these numbers are to be calculated and populated in these new fields.

description.png

I have also attached the .qvw I have created this far with the original data excel tables.

The problem is that I am not sure what I shall put there in 'X,' 'Y', 'Z', when creating these new fields.

Much appreciate your help. Thank you.


Refer_table:
Directory;
LOAD Parameter,
     
M28,
     
M27,
     
M26
FROM
[refer table.xls]
(
biff, embedded labels, table is Sheet2$);


Points_temp:
Directory;
LOAD Points,
     
DateMDY,
     
DateSerial
FROM
[original data.xls]
(
biff, embedded labels, table is Sheet1$);


Points:
LOAD DateSerial,
     
DateMDY,
     
Points,
      'X'
as newfield26,
      'Y'
as newfield27,
      'Z'
as newfield28
Resident [Points_temp];    

Drop Table Points_temp;

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Perhaps like this:

Refer_table:

LOAD Parameter,

  M28,

     M27,

     M26

FROM

[comm208647_refer table.xls]

(biff, embedded labels, table is Sheet2$);

Points_temp:

LOAD Points,

     DateMDY,

     DateSerial

FROM

[comm208647_original data.xls]

(biff, embedded labels, table is Sheet1$);

JOIN (Points_temp)

LOAD * Resident Refer_table;

Points:

LOAD *,

  rangesum(M26*Points,peek(M26,-1)*peek(Points,-5) ,peek(M26,-2)*peek(Points,-10),peek(M26,-3)*peek(Points,-15),peek(M26,-4)*peek(Points,-20)) as X,

  rangesum(M27*Points,peek(M27,-1)*peek(Points,-5) ,peek(M27,-2)*peek(Points,-10),peek(M27,-3)*peek(Points,-15),peek(M27,-4)*peek(Points,-20)) as Y,

  rangesum(M28*Points,peek(M28,-1)*peek(Points,-5) ,peek(M28,-2)*peek(Points,-10),peek(M28,-3)*peek(Points,-15),peek(M28,-4)*peek(Points,-20)) as Z

Resident [Points_temp]

ORDER By DateSerial, Parameter desc;    

Drop Table Points_temp;

FinalPoints:

Noconcatenate

LOAD * Resident Points Where Parameter = 0;

Drop Table Points;

Drop Field M26,M27,M28, Parameter from FinalPoints;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

Perhaps like this:

Refer_table:

LOAD Parameter,

  M28,

     M27,

     M26

FROM

[comm208647_refer table.xls]

(biff, embedded labels, table is Sheet2$);

Points_temp:

LOAD Points,

     DateMDY,

     DateSerial

FROM

[comm208647_original data.xls]

(biff, embedded labels, table is Sheet1$);

JOIN (Points_temp)

LOAD * Resident Refer_table;

Points:

LOAD *,

  rangesum(M26*Points,peek(M26,-1)*peek(Points,-5) ,peek(M26,-2)*peek(Points,-10),peek(M26,-3)*peek(Points,-15),peek(M26,-4)*peek(Points,-20)) as X,

  rangesum(M27*Points,peek(M27,-1)*peek(Points,-5) ,peek(M27,-2)*peek(Points,-10),peek(M27,-3)*peek(Points,-15),peek(M27,-4)*peek(Points,-20)) as Y,

  rangesum(M28*Points,peek(M28,-1)*peek(Points,-5) ,peek(M28,-2)*peek(Points,-10),peek(M28,-3)*peek(Points,-15),peek(M28,-4)*peek(Points,-20)) as Z

Resident [Points_temp]

ORDER By DateSerial, Parameter desc;    

Drop Table Points_temp;

FinalPoints:

Noconcatenate

LOAD * Resident Points Where Parameter = 0;

Drop Table Points;

Drop Field M26,M27,M28, Parameter from FinalPoints;


talk is cheap, supply exceeds demand
Not applicable
Author

Much appreciate your help Gysbert.


I do not get the correct result initially however a little twist from your great suggestion (& script) gives me what I want! Thank you very much.