topic About Syn in QlikView

About Syn

Mon, 03 Jan 2011 11:52:41 GMT

Hi All,

Could your experts explain what is the following meaning?

$Syn 1 = LocaleId+TimeId
$Syn 2 = LocaleId+OrderId+TimeId
$Syn 3 = LocaleId+MemberId+OrderId+TimeId
$Syn 4 = $Syn 1+$Syn 2
$Syn 5 = $Syn 1+$Syn 2+$Syn 3
$Syn 6 = $Syn 4+$Syn 5

I was wondering why there exists syn4,5,6 .

Thanks.

About Syn

prieper — Mon, 03 Jan 2011 22:34:07 GMT

In general it is advisable, not to tolerate synthetic keys but to replace & control the links between tables. Otherwise you always run into danger to have "strange" results and/or memory-problems due to cartesian products created.

HTH
Peter

About Syn

Tue, 04 Jan 2011 01:09:47 GMT

synthetic keys 4, 5, and 6 were created to handle overlaps between the previous synthetics.

ie: Synthetic 1 contains LocaleId and TimeId; Synthetic 2 contains these two and OrderId. Therefore another synthetic needs to be created (Syntheitc 4 in this case) to resolve the linking.

Just so you're aware, a document with this kind of linking schema will not do what you expect, even though the script appears to be running correctly to completion, making a selection on any of these tables will have results that are difficult to predict or understand.

In other words, these synthetics need to be addressed prior to further development.

About Syn

Tue, 04 Jan 2011 02:30:56 GMT

Hi Peter,

I always get the strange results when used syn key.

But how can I maintain a link table?

I have the following scenario,

Order:
load * Inline
[LocaleId,MemberId,OrderId,TimeId
1,1,1,1
1,2,2,1
];
Revenue:
load * Inline
[LocaleId,MemberId,OrderId,TimeId,Amount
1,1,1,1,30
];
ChargeTimes:
load * Inline
[LocaleId,OrderId,TimeId,ChargeTimes
1,1,1,1
1,2,1,0
];
Track:
load * Inline
[LocaleId,TimeId,Visits
1,4,10
2,2,10
3,1,10
];

You will find orders connects revenue with syn(LocaleId,MemberId,OrderId)

ChargeTimes uses syn(LocaleId,OrderId,TimeId)

Track uses syn(LocaleId,TimeId)

But after I load data into qvw, the results are not what I expected.

I use LocaleId as the selection: it has 1,2,3. three options.

But when I choose 1,2,3 and use a text to show:sum(Visits), it always gives me 0. But if I don't choose anything, Clear the selection, it will show the right num 30.

I don't know why, I really would like you to explain it and show me how do they join.

What I can do?

Isaac Li

About Syn

prieper — Tue, 04 Jan 2011 21:32:00 GMT

Hi Isaac,

not easy to solve, as I do not quite understand which data are behind.

You may simply concatenate the various data and build your formulas accordingly.

I tend to build a kind of backbone, which might be your orders table and then to assign the events to each of the orders.

HTH
Peter

About Syn

Oleg_Troyansky — Tue, 04 Jan 2011 22:24:10 GMT

It looks like Orders and Revenue are at the same level of detail, and therefore they can be easily combined into a single table using Joining. This will simplify your structure a lot.

The rest can be solved with a concatenated Fact table, as Peter suggested, or, if you want to take a chance and tolerate the remaining Synthetic keys, you can try and leave the data structure as is.