Stacked $Syn Keys with Null Values

[thomas_wang]

Hi All,

Is there anyone has some ideas about $Syn keys' behavior of Null values?

I have a fact table that contain several keys which are split into the $Syn table. Below a simple example, but it may not be accurate.

Fact:

LOAD * Inline [

KEY1, KEY2, KEY3, A, B, C

1,2,3,a,b,c

];

KEY1, KEY2, KEY3 are all split into the $Syn table. And the $Syn table has combined keys up to tens!

Then I insert a data that has a null value in KEY1.

Concatenate(Fact)

LOAD //Null() as KEY1,

    'x' as KEY2,

    'x' as KEY3,

    'x' as A

AutoGenerate 1;

This incomplete datum just want to show that there is a corresponding relationship between KEY2, KEY3 and A when the three are "x".

Then I create a Table Box with KEY2, KEY3 and A on the front end, and select "KEY2 = x".

It showed that A is "-". Rather than "x" as I expected.

And when I select "A = x", KEY2 and KEY3 showed "-", too.

In fact, if you just build a very simple data structure like the one I built, it's not reproducible.

In my problem data structure, there are too many combined keys and columns in the $Syn table.

I would be grateful if anyone could explain this phenomenon!

[jonathandienst]

Nested synthetic keys are quite difficult to interpret and almost always imply an improperly designed data model.

[thomas_wang]

I agree with you, but this is somebody else's masterpiece.╮(╯_╰)╭

[jonathandienst]

Then you have an unenviable problem, I am afraid.

[thomas_wang]

Yes, the functional test of this report has passed, and the functional development process has been closed.

Then they let me do Access control! (╯°Д°)╯︵ ┻━┻

[hic]

Jonathan is absolutely right when he says that "Nested synthetic keys are quite difficult to interpret". My take on it is that nested synthetic keys are a sign of an inherently incorrect data model. You cannot define what the behaviour should be in the first place.

On the NULLs - and this explains the behaviour you see: NULLs do not link to other tables. This means that if one table contains a record with Field1='x'; Field2='' (blank) and the other table contains a record with Field1='x'; Field2=NULL, these will not be associated. In my opinion, this is correct behaviour.

My advice is to correct the data model. This may be both technically and politically difficult since it is "somebody else's masterpiece", but it is nevertheless the correct thing to do.

HIC

[thomas_wang]

On the NULLs - and this explains the behaviour you see: NULLs do not link to other tables. This means that if one table contains a record with Field1='x'; Field2='' (blank) and the other table contains a record with Field1='x'; Field2=NULL, these will not be associated. In my opinion, this is correct behaviour.

This is a statement that can not justify itself. Because in a simple data model, it reverted to what I said was “right behavior“.

Fact:
LOAD * Inline [
KEY1, KEY2, KEY3, A, B, C
1,2,3,a,b,c
];

Dim:
LOAD * Inline [
KEY1, KEY2, KEY3, D
];

Concatenate(Fact)
LOAD //Null() as KEY1,
     'x' as KEY2,
'x' as KEY3,
'x' as A
AutoGenerate 1;

[Miguel_Angel_Baeyens]

You cannot "fake" synthetic keys, since they are not real in your example, you will neither experience nor you will be able to reproduce the issue you see with the "real" synthetic key.

Since synthetic keys are the result of two or more fields with the same exact name in two or more tables it's impossible to see, at a glance, unless you know from memory the data source, which results this synthetic key table will store.

In addition, INLINE tables never generate real NULL values, but blanks, as Henric mentioned above.

[thomas_wang]

In my opinion, Whether ”synthetic keys“ and "NULL" are "real" or not is not important. The point is how to figure out the behavior of QlikView™. We can introduce these concepts to describe the problem, and when these concepts can not be clearly described, we need to find alternatives. I do not quite understand how you define "real" and what circumstances it will produce. I thought my example was the same as precedent.

BTW, The ”NULL“ I mentioned is not produced by INLINE, but by Concatenate.

Thanks.

[thomas_wang]

I recreate this problem and make it simple.

A:
LOAD * Inline [
KEY1, KEY2, KEY3, A
1, 2, 3, a
];

B:
LOAD * Inline [
KEY1, KEY2, KEY3, B
1, 2, 3, b
];

C:
LOAD * Inline [
KEY1, KEY2, C
1, 2, c
];

Concatenate(A)
LOAD //Null() as KEY1,
     'x' as KEY2,
'x' as KEY3,
'x' as A
AutoGenerate 1;

Concatenate(A)
LOAD 'y' as KEY1,
'y' as KEY2,
'y' as KEY3,
'y' as A
AutoGenerate 1;

I figuring out the cause of the problem: Both Stacked(Nested) and NULLs.

I couldn't modify the original content I posted at the top, so I marked this as the "right answer" to get your attention. If I get a very good explanation, I will replace it.