topic Avoiding Syn Keys - Please help in QlikView

Avoiding Syn Keys - Please help

Tue, 18 May 2010 11:04:21 GMT

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.

Avoiding Syn Keys - Please help

Tue, 18 May 2010 11:15:00 GMT

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

Avoiding Syn Keys - Please help

Tue, 18 May 2010 11:19:05 GMT

Sorry for that, please see attached.

Avoiding Syn Keys - Please help

Tue, 18 May 2010 12:43:55 GMT

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.

Avoiding Syn Keys - Please help

Tue, 18 May 2010 12:51:55 GMT

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

Avoiding Syn Keys - Please help

Tue, 18 May 2010 12:53:04 GMT

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

Avoiding Syn Keys - Please help

Tue, 18 May 2010 13:03:54 GMT

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

Avoiding Syn Keys - Please help

RickWild64 — Tue, 18 May 2010 14:37:27 GMT

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

Avoiding Syn Keys - Please help

Oleg_Troyansky — Tue, 18 May 2010 15:44:55 GMT

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.

Avoiding Syn Keys - Please help

boorgura — Tue, 18 May 2010 16:04:56 GMT

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.

Avoiding Syn Keys - Please help

Tue, 18 May 2010 16:21:19 GMT

This is a SAP db, I see...

I had the same problem. I wanted to see INV and RIN as the SAME object, (but the minus sign on quantities and prices for RIN). So, what I did is: load first INV table giving it a name:

Document:

SELECT

....

FROM INV

And then, I loaded RIN table forcing the concatenation of the two tables:

CONCATENATE (Document) SELECT

.....

FROM INV

I am simplifying to show the method, hope it's clear. This will result in a single "Document" internal table without syntetics keys. Of course, you will have fields that you can use, if you need, to distinguish the invoice from the return, in the analysis. By the way, I used the same approach for DLN, RDN, RDR tables....

Avoiding Syn Keys - Please help

Wed, 19 May 2010 07:40:27 GMT

Thanks alot guys for all your help.

I got it working by using a combination of your methods.

Thanks again

Avoiding Syn Keys - Please help

Wed, 19 May 2010 08:11:39 GMT


Christo Roder wrote:
I got it working by using a combination of your methods. <div></div>

Can you please put the combination of those methods which was working finally.

Avoiding Syn Keys - Please help

Wed, 19 May 2010 11:36:05 GMT

I did a inner join on the Invoice Lines & Invoice Header (which joins on DocEntry).

Stored the joined table into a qvd file & then dropped the table.

I did a inner join on the Credit Note Header & Credit Note Lines (also joins on DocEntry)

Stored the joined table into a qvd file & then dropped the table.

I then loaded the INV qvd file and then CONCATENATE it with the RIN qvd file.

Now I have one table (document). See attached how this looks now.

Thank you for all your help.