8 Replies Latest reply: Nov 15, 2015 4:46 PM by Stefan Wühl

# Generating the missing data with previous data

Hi All,

I have a scenario , if my previous data is missing it should copy the last value available in the previous row based on a condition.

Lets say I have a table shown below and if my customer ,branch, and month should be same as previous rows only then it should copy the revious value. In this case it should copy 3000 for all the below rows. I am trying to do this in the script.

Current

CustomerBranchMonthValue
Customer1Branch1Jan1000
Customer1Branch1Jan
Customer1Branch1Jan
Customer1Branch1Jan
Customer2Branch1Feb2000
Customer2Branch2Feb2100
Customer3Branch2Feb3000
Customer3Branch2Feb
Customer3Branch2Feb
Customer3Branch2Feb
Customer3Branch2Feb
Customer3Branch2Feb

After I made a condition like if(customer = peek(customer) and Branch = peek(Branch) and Month = Peek(Month),peek(value))

It just copies the value to the immediate below row but I want it to be copied to all the rows as per my request. here 3000 should be copied to the below 5 lines as it satisfies my condition. Same with 1000 it should be copied to immediate 3 rows.

Can any one help me with this please.

Thanks,

Lalitha

• ###### Re: Generating the missing data with previous data

Try this assuming your table is sorted as in above sample, if not, use a ORDER BY clause in your original table LOAD.

Branch,

Month,

//    Value as Value2,

If(Len(Trim(Value)),

Value,

if(Customer = peek(Customer) and Branch = peek(Branch) and Month = Peek(Month), peek(Value))

)                AS Value

FROM

(html, codepage is 1252, embedded labels, table is @1);

• ###### Re: Generating the missing data with previous data

Hi Swuehl,

Thanks for your reply. I am doing the same thing in my script and using the order by as well.

Please find the attached qlikview file.

Due to same rows for customer3 it is not showing the multiple rows in Table box.

But I hope you have understood my requirement.I want to copy the data if my above mentioned condition satisfies irrespective of any number of rows.

• ###### Re: Generating the missing data with previous data

It should work if you use the script I posted above and not only part of.

In addition (but that's not failing your script), I've converted the Month to a dual value, so it sorts chronological.

• ###### Re: Generating the missing data with previous data

Hi Swuehl,

Thanks for your reply..  I have changed the data little bit so it would be more clear.

the value 14850 of week 42 should be copied to week 43 and week 44.

• ###### Re: Generating the missing data with previous data

Yes. Understood. I believe I am not posting my solution a third time, unchanged to the first (ok, except the change for Peek(Week) = Week removed in the condition, because it's a new requirement that you want to get the value from previous week).

Can you tell me what's wrong with my solution, and why you keep changing the script in a way that's it not working anymore?

I do get this result:

Customer Branch Week Value TMP
Customerbranch12014/301038810388
Customerbranch12014/3110388
Customerbranch12014/321038810388
Customerbranch12014/331047210472
Customerbranch12014/3410472
Customerbranch12014/351161611616
Customerbranch12014/361293112931
Customerbranch12014/3712931
Customerbranch12014/381344813448
Customerbranch12014/391382813828
Customerbranch12014/401417214172
Customerbranch12014/4114172
Customerbranch12014/421485014850
Customerbranch12014/4314850
Customerbranch12014/4414850
Customerbranch12014/451586415864
Customerbranch12014/461637116371
Customerbranch12014/4716371
Customerbranch12014/481716917169
Customerbranch12014/491784317843
Customerbranch12014/5017843
Customerbranch12014/511835018350
Customerbranch12014/521869418694
Customerbranch12015/011892218922

TMP is only to show the original values of 'Value' as the are read in. You can remove the TMP field from the script if you want:

Customer,Branch,Week, // Value as TMP,

If(Len(Trim(Value)), Value,

if(Customer= peek(Customer) and Branch = peek(Branch) ,peek(Value))) as Value

Resident Test order by Customer,Branch,Week;

• ###### Re: Generating the missing data with previous data

Hi Swuehl,

Sorry for that.. In the initial script due to same rows duplicated I was not able to understand it.

In my script I deal with nulls I mean here its the blank space with the test data. I tried the same script and instead of checking len(trim(value)) I used null but I dnt know the reason I dont see the desired result of copying the same data to the below rows if my condition is met.

Thanks for the solution.

Lalitha

• ###### Re: Generating the missing data with previous data

Hello Latitha,

you can fill missing values using Qlikview transformation functions.

Select "Enable transformation Step" in file wizard

then select "Fill"  Tab

...and click on "Fill..." Button

Now you can specify your fill strategy selecting target column (4, in your case), cell condition (is empty) and fill type (Above, in your case)

then press "Next" button and finally press "Finish" button.

At the end the load instruction have to looks like the following script:

Branch,
Month,
Value
FROM
FillData.xlsx
(
ooxml, embedded labels, table is Sheet1, filters(
Replace(4, top, StrCnd(null))
));