Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

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:

LOAD

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;

8 Replies
MVP
MVP

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.

LOAD Customer,

     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

[https://community.qlik.com/thread/191491]

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

Not applicable

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.

Thanks alot for your time..

MVP
MVP

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.

Not applicable

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.

Could you please help me with this..

Thanks for your time.

MVP
MVP

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:

LOAD

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;

anlonghi2
Contributor II

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

EnableTransf.png

then select "Fill"  Tab

Fill.png

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

Fillbutton.png

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)

fillcells.png

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

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

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

Reload data and...good luck !!!


Best regards

Andrea

Not applicable

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

MVP
MVP

Re: Generating the missing data with previous data

No problem, glad your issue is resolved now.

Community Browser