Populate from Previous MULTIPLE rows

nabilbrahimi — Wed, 07 Nov 2018 13:29:43 GMT

Hi,

I have the next issue where I would like to ask for a tip

In the below screen "Current View "for rows where I have Missing Value Flag = 1, I want populate missing data from the previous rowS (it could be many)

Basically I need to populate missing values from from the latest available values prior to flag = 1

Current view

Final View would be:

Thank you for the help!

Re: Populate from Previous MULTIPLE rows

petter — Wed, 07 Nov 2018 16:53:26 GMT

If the assumption I made that what would be copied from previous rows are always six rows as it seems to be in your sample data the following script will work:

LOAD 
     RowNo() AS R#,
     Date, 
     [% KEY_Table_A], 
     [Original % KEY_Table_A], 
     $_CODE_Plant_Year_Month, 
     CODE_Plant, 
     CODE_Year_Month_Change, 
     Missing_Value, 
     Code_Plant, 
     CODE, 
     If(  Missing_Value , Peek('CODE_SUB',RowNo()-7) , CODE_SUB) AS CODE_SUB, 
     If(  Missing_Value , Peek('_Code_Price',RowNo()-7) , _Code_Price) AS _Code_Price,
     If(  Missing_Value , Peek('_Code_Sub_Price',RowNo()-7) , _Code_Sub_Price) AS _Code_Sub_Price, 
     If(  Missing_Value , Peek('_Code_Price_',RowNo()-7) , _Code_Price_) AS _Code_Price_,
     If(  Missing_Value , Peek('_Code_Sub_Price_',RowNo()-7) , _Code_Sub_Price_) AS _Code_Sub_Price_
WHILE
  Not(Missing_Value) AND IterNo()=1
  OR
  Missing_Value AND IterNo()<=6;
LOAD
     Date, 
     [% KEY_Table_A], 
     [Original % KEY_Table_A], 
     $_CODE_Plant_Year_Month, 
     CODE_Plant, 
     CODE_Year_Month_Change, 
     Missing_Value, 
     Code_Plant, 
     CODE, 
     CODE_SUB,
     _Code_Price, 
     _Code_Sub_Price, 
     _Code_Price AS _Code_Price_, 
     _Code_Sub_Price AS _Code_Sub_Price_
FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

The two last columns in your CSV had the same as the two columns to the left of them - so I had to rename them first - that's why the lines from line 21 to 38 are necessary. If your CSV has columns with unique column names you can do all without any preceding load and simplify the load script.

Re: Populate from Previous MULTIPLE rows

nabilbrahimi — Thu, 08 Nov 2018 09:40:13 GMT

Well that's the trick ( I didn't mentioned ), in this example I have 6 rows, but can by any (4, 5,10...) it changes by the time. Other Keys (% Key_Table_A) could have different number of rows.

topic Re: Populate from Previous MULTIPLE rows in QlikView

Populate from Previous MULTIPLE rows

Re: Populate from Previous MULTIPLE rows

Re: Populate from Previous MULTIPLE rows