Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
what you want actual output
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
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:
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 !!
Not a problem