Segeregate your data column into two different column like one would be numeric and another one would be text. your code should be some thing similar as below
if( len(purgechar(Field,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) >0,Field) as Numeric_Field, if( len(purgechar(Field,'1234567890')) >0,Field) as String_Field,
Your data will look like this
Numeric_Field String_Field
- abcd
- bcde
- cdef
001 -
- defg
- efgh
- f ghi 0002 -
Once you get the data in this format.
Step 2:
Fill your missing data in Numeric_Field with peek() function.
Once you do this you will get you required format.
I dnt have QV installed in my system now with me, so i cant send you a sample app.
Thanks both for your input. The sample qlikview file looks great!
I've a little trouble understanding the logic. Can you explain in plain english what the code below does?
LEFT JOIN (C) LOAD Sequence ,Field1 ,if(exists(Field2,Field1),Field1,peek(Field2)) as Field2 RESIDENT C ORDER BY Sequence DESC ; INNER JOIN (C) LOAD Sequence RESIDENT C WHERE Field1<>Field2
karuna wrote: The sample qlikview file looks great! I've a little trouble understanding the logic. Can you explain in plain english what the code below does?
Well, that's from the script I wrote when somone asked the exact same question in the below thread three days ago. Are you both working on the same project? Is this some sample exercise given in a QlikView class? I'm just curious why it's being asked twice in the space of three days.
LEFT JOIN (C) LOAD Sequence ,Field1 // This field isn't necessary and can be removed ,if(exists(Field2,Field1),Field1,peek(Field2)) as Field2 RESIDENT C ORDER BY Sequence DESC ;
The join is being done from C back onto itself on both Sequence and Field1. Field1 can be removed since the record is already uniquely identified by Sequence. I didn't catch that when I first wrote this. Then we're adding a Field2 to C, defined like this:
,if(exists(Field2,Field1),Field1,peek(Field2)) as Field2
This says, "If the value of Field1 is an existing value of Field2 (i.e., either 0001 or 0002), then use Field 1. If it isn't (i.e., one of the alphabetic strings), then look at the row above this one in table C, and grab the value of Field2 from it instead." Applying that to our table C, and keeping in mind that we ordered by sequence DESCENDING:
Sequence, Field1, Field2 8,0002,0002 <-- 0002 is an existing Field2 value, so we use it 7,fghi,0002 <-- fghi isn't an existing Field2 value, so we look at the previous row (sequence 😎 and grab Field2 from there 6,efgh,0002 <-- efgh isn't an existing Field2 value, so we look at the previous row (sequence 7) and grab Field2 from there etc.
Next we do this join:
INNER JOIN (C) LOAD Sequence RESIDENT C WHERE Field1<>Field2 ;
An inner join says to only keep rows from table C that ALSO exist in what we're loading in our load statement. What we're loading in our load statement are only those rows from table C where Field1 is not the same as Field2. So what this says is to DELETE any rows in table C where Field1 EQUALS Field2. Those are the rows with the numeric codes in Field1. So the inner join will delete all rows with a numeric code in Field1. Now, I could have just checked if Field1 was numeric, but I was trying to assume that the code values we're applying might not always be numeric, but would instead be identified by a separate load (table B).
As far as I know this is not part of some certifcation process 😉
Thank you kindly for the thorough explanation and text. This really helps alot to understand the code used to generate the correct outcome for this problem.
