Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
thomas_wang
Creator
Creator

Stacked $Syn Keys with Null Values

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.

$Syn.jpg

I would be grateful if anyone could explain this phenomenon!

18 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
thomas_wang
Creator
Creator
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

Then you have an unenviable problem, I am afraid.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
thomas_wang
Creator
Creator
Author

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
Former Employee
Former Employee

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
Creator
Creator
Author

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


捕获.JPG


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
Creator
Creator
Author

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
Creator
Creator
Author

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;

捕获.JPG

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.