topic Separating keys from data in one table in QlikView

Separating keys from data in one table

Fri, 17 Sep 2010 10:32:33 GMT

Dear QV-gurus,

My datasource is a table consisting of one column which for example contains the following data

abcd

bcde

cdef

0001

defg

efgh

fghi

0002

THe numeric values are the keys for the textvalues above it. The data needs to be transformed so that it outputs the following:

0001 abcd

0001 bcde

0001 cdef

0002 defg

0002 efgh

0002 fghi

How do I go about this?

Separating keys from data in one table

Anonymous — Fri, 17 Sep 2010 11:36:52 GMT

Hi karuna can you reffered this qv file. It will help you.

Separating keys from data in one table

Fri, 17 Sep 2010 11:37:33 GMT

Hi Karuna,

Logic for achieving this should be as follow,

Step 1:

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.

Try this above step, u`ll get the desired result.

Hope this will help you

-Sridhar

Separating keys from data in one table

Fri, 17 Sep 2010 12:49:18 GMT

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

Thanks in advance

Separating keys from data in one table

Anonymous — Fri, 17 Sep 2010 13:21:11 GMT

Hi karuna. You go to the edit script->debug.there you will get the steps, please refer..

Separating keys from data in one table

johnw — Sat, 18 Sep 2010 00:05:22 GMT


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.

http://community.qlik.com/forums/t/34049.aspx

Anyway, at the point in the script that you're asking about, table C looks like this:

Sequence, Field1
1,abcd
2,bcde
3,cdef
4,0001
5,defg
6,efgh
7,fghi
8,0002

And we also have a table B:

Field2
0001
0002

And now we do this join:

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

Separating keys from data in one table

Thu, 23 Sep 2010 07:08:59 GMT

John,

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.

Kind regards,

//kAruna