Total items by ID in Script

[#1]

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.

[Anonymous]

what you want actual output

[Kushal_Chawda · Accepted solution]

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

[tamilarasu]

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:

[#5]

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

[Kushal_Chawda]

Not a problem