Try this assuming your table is sorted as in above sample, if not, use a ORDER BY clause in your original table LOAD.
// Value as Value2,
if(Customer = peek(Customer) and Branch = peek(Branch) and Month = Peek(Month), peek(Value))
) AS Value
(html, codepage is 1252, embedded labels, table is @1);
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..
testing data.qvw 153.5 K
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.
testing data_2.qvw 152.2 K
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.
testing data.qvw 151.8 K
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:
Customer,Branch,Week, // Value as TMP,
if(Customer= peek(Customer) and Branch = peek(Branch) ,peek(Value))) as Value
Resident Test order by Customer,Branch,Week;
comm191491_2.qvw 151.0 K
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.
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:
(ooxml, embedded labels, table is Sheet1, filters(
Replace(4, top, StrCnd(null))
Reload data and...good luck !!!