Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Total items by ID in Script

Hi, I'm really stuck on a project I've been given and hope you can help me. I have to create a flag field for the following table of data. My actual data contains thousands of different models.

What I need to do is to flag the rows where the sum of the accumulated values of %_OF_TOTAL gets to 1 and then ignore the rows after, see desired results below.

    I would be so grateful for any help, thank you in advance.

1 Solution

Accepted Solutions

Re: Total items by ID in Script

I think for 6256 Model ID accumulated values are wrong in your case.

Data:
LOADMODEL_ID,
LINE_NUMBER,
%_OF_TOTAL
FROM
C:\Users\kush\Desktop\Data.xlsx
(
ooxml, embeddedlabels, tableis Sheet1);

New:
LOAD *,
if(MODEL_ID<>Previous(MODEL_ID),%_OF_TOTAL,rangesum(Peek('ACCUMULATED'),%_OF_TOTAL)) asACCUMULATED
Resident Data
OrderbyMODEL_ID, LINE_NUMBER;

DROPTable Data;

New1:
LOAD *,

if(MODEL_ID<>Previous(MODEL_ID),1,

if(MODEL_ID=Previous(MODEL_ID) and (floor(ACCUMULATED)<=1 and previous(floor(ACCUMULATED))<>1),Peek(INCLUDE_FLAG))) asINCLUDE_FLAG
Resident New;

DROPTable New;

Final:
NoConcatenate
LOAD *
Resident New1
where INCLUDE_FLAG=1;


DROPTable New1;



NOTE : IF you just want to Flag the Rows then Comment Final Table LOAD

By commenting the Final table you will get output like below

View solution in original post

5 Replies
vsudhakar
Contributor III

Re: Total items by ID in Script

what you want actual output

Re: Total items by ID in Script

I think for 6256 Model ID accumulated values are wrong in your case.

Data:
LOADMODEL_ID,
LINE_NUMBER,
%_OF_TOTAL
FROM
C:\Users\kush\Desktop\Data.xlsx
(
ooxml, embeddedlabels, tableis Sheet1);

New:
LOAD *,
if(MODEL_ID<>Previous(MODEL_ID),%_OF_TOTAL,rangesum(Peek('ACCUMULATED'),%_OF_TOTAL)) asACCUMULATED
Resident Data
OrderbyMODEL_ID, LINE_NUMBER;

DROPTable Data;

New1:
LOAD *,

if(MODEL_ID<>Previous(MODEL_ID),1,

if(MODEL_ID=Previous(MODEL_ID) and (floor(ACCUMULATED)<=1 and previous(floor(ACCUMULATED))<>1),Peek(INCLUDE_FLAG))) asINCLUDE_FLAG
Resident New;

DROPTable New;

Final:
NoConcatenate
LOAD *
Resident New1
where INCLUDE_FLAG=1;


DROPTable New1;



NOTE : IF you just want to Flag the Rows then Comment Final Table LOAD

By commenting the Final table you will get output like below

View solution in original post

Re: Total items by ID in Script

Hi Simon,

Try like below.

Data:

LOAD MODEL_ID,

     LINE_NUMBER,

     %_OF_TOTAL

FROM

(ooxml, embedded labels, table is Sheet1);


NoConcatenate

Load MODEL_ID,

     LINE_NUMBER,

     %_OF_TOTAL,

     If(Peek('MODEL_ID') <> MODEL_ID, %_OF_TOTAL, Peek('ACCUMULATED')+%_OF_TOTAL ) as ACCUMULATED,

     If(Peek('MODEL_ID') <> MODEL_ID or Peek('ACCUMULATED')+%_OF_TOTAL < 1 or Peek('ACCUMULATED')<1 and Peek('ACCUMULATED')+%_OF_TOTAL > 1,1) as INCLUDE_FLAG

     Resident Data Order by MODEL_ID, LINE_NUMBER;


DROP Table Data;

Output:

Untitled.png

Not applicable

Re: Total items by ID in Script

Hi, this was great, had to tweek it a little when I introduced it to my full data set, but Thank You, a huge help !!

Re: Total items by ID in Script

Not a problem