topic Re: Link table - odd issue in QlikView

Link table - odd issue

jrdunson — Wed, 13 Jan 2016 22:04:27 GMT

Hey, I'm trying to build a link table, using a common 'superkey' (call skey). I've edited down my original (hoping I didn't miss syntax)

Here on one table

[Tracking]:

LOAD

,Text(id1) as [Tracking ID]

,Text(id2) as [Tracking ID2]

,Text(id1) & '.' & Text(id2) as [skey]

SQL SELECT

id1

,id2

from tracking;

which will create this (for example)... which is precisely what I want...

Tracking ID Tracking ID2 skey

0580003657 0000562378 0580003657.0000562378

And a second

[Task]:

LOAD

,Text(id1) as [Task ID]

,Text(id2) as [Task ID2]

,Text(id1) & '.' & Text(id2) as [skey]

SQL SELECT

id1

,id2

from task;

which should create this:

Task ID Task ID2 skey

0580003657 0000563127 0580003657.0000563127

Instead, it creates this:

Task ID Task ID2 skey

0580003657 0000563127 0580003657.0000562378

And in my link table:

[Skey_Link]:

Load

[Tracking ID] as [ID]

,[Tracking ID2] as [Corr_ID]

,skey

Resident Tracking

;

Concatenate [Skey_Link]:

;

Load

[Task ID] as [ID]

,[Task ID2] as [Corr_ID]

,skey

Resident Task

;

I get this:

0580003657 0000563127 0580003657.0000562378

0580003657 0000562378 0580003657.0000562378

instead of this

0580003657 0000563127 0580003657.0000563127

0580003657 0000562378 0580003657.0000562378

I want the skey for all three tables... Tracking, Tasks, and Skey_Link...

Is there sort of a 'no concatenate' I can use ...when a common field is used in different tables?

Re: Link table - odd issue

andrespa — Thu, 14 Jan 2016 07:56:37 GMT

Hi Jarrell,

It seems to me that could be a data problem instead of a modelling problem. Nevertheless, could you post some sample data to have a look and maybe someone could help you a bit more.

Cheers,

Andrés

Re: Link table - odd issue

jrdunson — Thu, 14 Jan 2016 15:27:09 GMT

Hey Andres, thanks... but not sure it is a data issue... here is more:

For the skey field,

- Step 1: For the first load statement, the value of concatenated IDs [0580003657.0000562378] is loaded into the skey field. The associated fields [id2, id2, or 0580003657, 0000562378 respectively ] are also loaded as normal:

skey, id1, id2

[0580003657.0000562378], 0580003657, 0000562378 [OK]

- For the second load statement, the valve of concatenated IDs [0580003657.0000563127] is not loaded. Rather, the concatenated skey from Step 1 [0580003657.0000562378] is associated with

the new set of ids [id2, id2, or 0580003657, 0000563127 respectively], so now the result is an error:

skey, id1, id2

[0580003657.0000562378], 0580003657, 0000563127 [Error]

Here is what I've tried

----------------------------------------------------------------------------------------

I. When I comment-out one statement, the other works fine:

-- if Step 1 is commented out (in the load script), the second statement works as expected: [0580003657.0000563127], is associated with 0580003657, 0000563127

-- if Step 2 is commented out, the first works as expected: [0580003657.0000562378], is associated with 0580003657, 0000562378

----------------------------------------------------------------------------------------

II. When I switch the order of statements, the same effect occurs, but reverse:

[0580003657.0000563127], 0580003657, 0000563127 [OK]

[0580003657.0000563127], 0580003657, 0000562378 [Error]

- In each case, it is if, the second load cannot add a new value to a previous field [e.g. skey], if the value did not already exist in that field.

for example, if [0580003657.0000562378] exists, and [0580003657.0000563127] does not, then [0580003657.0000563127] cannot be loaded into the field ... which raises the question... is this a concatenation issue ?

----------------------------------------------------------------------------------------

III. I also tried changing the field name...[using 'skey0' for the second load, rather than 'skey']:

so, for the second table, instead of skey, id1, id2 , I used skey0, id1, id2. Here is what I got:

skey0, id1, id2

[0580003657.0000563127], 0580003657, 0000563127 [OK]

so, my data was:

Tracking

skey, id1, id2

[0580003657.0000562378], 0580003657, 0000562378 [OK]

Task0

skey0, id1, id2

[0580003657.0000563127], 0580003657, 0000563127 [OK]

so far so good... but, when I do this:

Tasks

load

skey0 as skey,

id1,

id2

Resident Task0;

// drop table Task0;

then here is what I get:

Tracking

skey, id1, id2

[0580003657.0000562378], 0580003657, 0000562378 [OK]

Tasks

skey, id1, id2

[0580003657.0000562378], 0580003657, 0000563127 [Error]

Merely doing a rename of a field, changes the value: ... something happens in the data model... that changes the value of skey0 [0580003657.0000563127] from [Task0] to the skey [0580003657.0000562378] int [Tasks]!

This is really odd...

IV. I'm using Microsoft SQL Server in the background... I've tried recreating the whole process in MS Excel... and everything works fine! I intended to reproduce the error and send a QVW... but cannot with MS Excel (because I'm not yet able to reproduce the error with MS Excel... [I'll try more]...)

so, it may be as you say, a data error... but it may be some kind of concatenation error ... for the same datafield, across two tables...

----------------------------------------------------------------------------------------

IV.

I tried to create just a supertable...

[skey_reference]:

Load

Text(id1) & '.' & Text(id2) as [skey]

SQL SELECT

id1

,id2

from tracking;

concatenate

Load Text(id1) & '.' & Text(id2) as [skey]

SQL SELECT

id1

,id2

from task;

when I looked at a table object, all I has was:

[0580003657.0000562378] [Error]

... there should be two rows:

[0580003657.0000562378]

[0580003657.0000563127]

[Again, the order makes a difference.

if I load task first, I just get [0580003657.0000563127] , not both

if I load tracking first, I just get [0580003657.0000562378] , not both

Again, it as if the Qlikview won't concatenate a new value to a currently existing field...

...I'll keep working on this as I can...

Any suggestions?

Re: Link table - odd issue

Thu, 14 Jan 2016 15:41:59 GMT

There is a noconcatenate statement you can add between the tables. Another option would be to qualify each table so the table name becomes part of the field name, and then just unqualify the key.

From your example, it is grabbing the id2 from the first table

Re: Link table - odd issue

jrdunson — Thu, 14 Jan 2016 16:02:12 GMT

I'll go try this and see... thanks