Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Kushal_Chawda

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
Creator III
Creator III

what you want actual output

Kushal_Chawda

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

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
Author

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