Avoiding Syn Keys - Please help

[#1]

Hi guys

I am busy with a sales report, but are having a problem with all the Syn keys. I am accessing SAP Business One Tables.

I have a table OINV (Invoice Header) which I left joined with INV1 (Invoice Lines) as INV

I have a table ORIN (Credit Memo Header) which I left joined with RIN1 (Credit Memo Lines) as RIN

My Sales and Qty's gets calculated by taking the fields of INV and subtrackting the fields of RIN

Both INV & RIN has the same fields & automatically gets linked together.

The problem is when I add another table. (see this example)

Both INV & RIN has a field called ItemCode. As soon as I add another table that contains the fields ItemCode & ItemName, I get

a Syn key on ItemCode. This is only one instance.

At the moment I have Syn keys for ItemCode, SlpCode, WhsCode etc.

[#2]

Hi,

Bit confused seeing your question. Can you please attach the screen shot of internal table viewer so that it is understandable.

Thanks and Regards,

Rikab Kothari

[#3]

Sorry for that, please see attached.

[#4]

Hi, try put alias in the field ItemCode for the table INV someting like this

Load

.. PCGCode,

ItemCode as ItemCode_Key,

ItemCode, ...

From RIN

Regards,

Christian Seijas E.

[#5]

Hi

As QlikView is linking automatically by Field Name you always have to make sure you get what you want.

You can either alias the fields as suggested by Christian or make use of the QUALIFY, UNQUALIFY statements (you might want to read through the manual or the help file for this).

It allows you to have the table name added to your field name so the 2 fields from different tables will not automatically match.

You can also follow a pattern where you always preceed linking fields with e.g. a "%" character and then use

UNQUALIFY "%*";

to force the correct linking and prevent unwanted ones.

Juerg

[#6]

Thank you for the reply.

I am not sure if this would work.

The thing is I do need to link all those fields together. If I give the one an alias, they won't tie up anymore.

I would like to know if there is a way to link those fields together without creating syn keys?

Regards

[#7]

Hi

You get syn keys for tables using more than one field as unique identifier. To avoid this you would need to create a surrogate key built from the multiple key fields using e.g. Autonumber, Autonumberhash128 or Autonumberhash256. Have a look at Book 1, page 404ff.

Juerg

[RickWild64]

I would create a compound surrogate key like this:

load

SlpCode
& DocDate
& CardCode
& PCGCode
& ItemCode
& WhsCode as INV_KEY

in both INV and RIN - or use autonumber, autohash or hash if you never want to look at the surrogate key.

This will tie RIN to INV correctly.

To link to Item Master, Sales Person etc, you need to leave the code exposed on on or other of INV or RIN, but not both.

Assuming you can't have a credit note without an invoice, load ItemCode, SlpCode etc in INV, but not in RIN.

In cases where you could have an entry in either or both tables (budget - actual case), I would concatenate the tables into a single table using a row-type identifier. Qlikview aggregates correctly according to the common field values, and you can untangle the rows using set analysis where necessary

[Oleg_Troyansky · Accepted solution]

Your problem needs the classic "Link Table" data model. You can find a description of it in a "Developer II" class materials, or simply search this site of "Link Tables" - there are plenty of threads and I think some Wiki pages describing the technique.

[boorgura]

Indentify the fields to be JOINED on - create a hash value of those (say named as KEY), and rename everything else.

similarly even in the second table do the same - such that no field name is common in both the tables except for KEY.

Then it will be a simple join.

Let me know if it works.