topic Generating the missing data with previous data in QlikView

Generating the missing data with previous data

Sat, 14 Nov 2015 12:24:56 GMT

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

Customer	Branch	Month	Value
Customer1	Branch1	Jan	1000
Customer1	Branch1	Jan
Customer1	Branch1	Jan
Customer1	Branch1	Jan
Customer2	Branch1	Feb	2000
Customer2	Branch2	Feb	2100
Customer3	Branch2	Feb	3000
Customer3	Branch2	Feb
Customer3	Branch2	Feb
Customer3	Branch2	Feb
Customer3	Branch2	Feb
Customer3	Branch2	Feb

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

swuehl — Sat, 14 Nov 2015 13:01:08 GMT

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);

Re: Generating the missing data with previous data

Sat, 14 Nov 2015 13:30:06 GMT

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

Re: Generating the missing data with previous data

swuehl — Sat, 14 Nov 2015 17:32:34 GMT

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

Sun, 15 Nov 2015 09:23:33 GMT

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.

Re: Generating the missing data with previous data

swuehl — Sun, 15 Nov 2015 13:28:22 GMT

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
Customer	branch1	2014/30	10388	10388
Customer	branch1	2014/31	10388
Customer	branch1	2014/32	10388	10388
Customer	branch1	2014/33	10472	10472
Customer	branch1	2014/34	10472
Customer	branch1	2014/35	11616	11616
Customer	branch1	2014/36	12931	12931
Customer	branch1	2014/37	12931
Customer	branch1	2014/38	13448	13448
Customer	branch1	2014/39	13828	13828
Customer	branch1	2014/40	14172	14172
Customer	branch1	2014/41	14172
Customer	branch1	2014/42	14850	14850
Customer	branch1	2014/43	14850
Customer	branch1	2014/44	14850
Customer	branch1	2014/45	15864	15864
Customer	branch1	2014/46	16371	16371
Customer	branch1	2014/47	16371
Customer	branch1	2014/48	17169	17169
Customer	branch1	2014/49	17843	17843
Customer	branch1	2014/50	17843
Customer	branch1	2014/51	18350	18350
Customer	branch1	2014/52	18694	18694
Customer	branch1	2015/01	18922	18922

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;

Re: Generating the missing data with previous data

anlonghi2 — Sun, 15 Nov 2015 15:34:26 GMT

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:

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

Re: Generating the missing data with previous data

Sun, 15 Nov 2015 18:30:22 GMT

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

swuehl — Sun, 15 Nov 2015 21:46:49 GMT

No problem, glad your issue is resolved now.

Re: Generating the missing data with previous data

swapnahari — Mon, 21 Dec 2020 05:43:26 GMT

Hi Sweuhl

I used the script which you shared in the qliksense but itseems to not work. Can you help!

Regards

Swapna