topic Re: Total items by ID in Script in QlikView

Total items by ID in Script

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Total items by ID in Script

Anonymous — Sun, 15 May 2016 10:13:39 GMT

what you want actual output

Re: Total items by ID in Script

Kushal_Chawda — Sun, 15 May 2016 11:17:47 GMT

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

Re: Total items by ID in Script

tamilarasu — Sun, 15 May 2016 11:22:33 GMT

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:

Re: Total items by ID in Script

Mon, 23 May 2016 15:11:44 GMT

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

Kushal_Chawda — Mon, 23 May 2016 17:54:43 GMT

Not a problem