topic Re: Need a help on multiple Synthetic Key in QlikView

Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 06:35:27 GMT

i m Creating a Practice Demo on Inventory Management.. in this i have a 3 Table.. Consumption , Order and Physical Stock..

But the Problem is that if i m Loading table Multiple Synthetic Key get generated..

Consumtion Table Column are,

[FA Type],

[Part Number],

[Part Description],

[Serial Number],

[Mat. Class],

[Mat. Type],

[ATA Chapter],

Tool,

[Cost Center],

Receiver,

[Booking Date],

[Consumed Qty],

Amount

and Order Table Column are,

[FA Type],

[Order No.] as [Order Number],

[Order State],

Date as [Order Date],

[Part No.] as [Part Number],

[Serial No. / Batch No.] as [Serial Number],

[Label No.] as [Label Number],

Description as [Part Description],

Qty. as OrderQty,

Rate,

[Invoice No.],

[Invoice Amt.],

[Material Type],

[Default Supplier],

so how to link this .. can we Join both the table in one and Classify and the data with Flag of Consumption and order.. Either any Better Way

Re: Need a help on multiple Synthetic Key

krishna20 — Tue, 07 Jul 2015 06:47:19 GMT

[FA Type],

[Part Number],

[Part Description],

[Serial Number],

[Mat. Class],

[Mat. Type],

[ATA Chapter],

Tool,

[Cost Center],

Receiver,

[Booking Date],

[Consumed Qty],

Amount

and Order Table Column are,

[FA Type],

[Order No.] as [Order Number],

[Order State],

Date as [Order Date],

[Part No.] as [Part Number],

[Serial No. / Batch No.] as [Serial Number],

[Label No.] as [Label Number],

Description as [Part Description],

Qty. as OrderQty,

Rate,

[Invoice No.],

[Invoice Amt.],

[Material Type],

[Default Supplier],

You have renamed with the same names as in the above table. If you need to link a table to another one you should have only one similar field . If the data is similar in the both tables join the both tables.

Hope it helps.

Re: Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 06:57:45 GMT

First see check the vales in both common feilds. and the basis of your requirement u want to use. if u want the values based on [FA Type] then put that feild. or make a key combination of all common feilds in all tables basing on that perform join

Re: Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 06:59:12 GMT

Hi Brijesh,

Very simple to do, Just rename where ever your getting synthetic keys or else just comment that field or else erase that

field.

Ramya.

Re: Need a help on multiple Synthetic Key

marksmunich — Tue, 07 Jul 2015 07:07:01 GMT

Rename or comment the Fields to prevent The joins, It eliminates the synthetic keys. else you can eliminate it from loading in to the script. First Make sure which joins are important between the tables.

Hope it is helpful.

Marks

Re: Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 07:10:54 GMT

Re: Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 07:11:41 GMT

All the Common Field having a Same Type of Data but Diff Value.. so i cant Remove That.. Because in that no Primary Key table in not Available both table Contains Foreign Key Value Only.

Re: Need a help on multiple Synthetic Key

MayilVahanan — Tue, 07 Jul 2015 07:12:08 GMT

Try like below

Create link table between these table

Create the Composite key and link with that key.

Re: Need a help on multiple Synthetic Key

jonathandienst — Tue, 07 Jul 2015 07:25:13 GMT

Your data looks like a candidate for concatenating into a single fact table. Ensure that fields that contain the same information are named the same in both tables, and add a derived field to identify the source.

Data:

LOAD

[FA Type],

[Part Number],

[Part Description],

[Serial Number],

...

[Consumed Qty],

Amount,

'Consumption' As Source

FROM ...

Concatenate(Data)

LOAD

[FA Type],

[Order No.] as [Order Number],

[Part No.] as [Part Number],

Description as [Part Description],

...

Qty as [Order Qty],

'Order' As Source

FROM ...

This will make the expressions much simpler and better performing than a data structure that has multiple fact tables and link tables.

If your data set is small (say less than a few million rows), then this probably will not matter as much.

Re: Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 07:55:43 GMT

Thanks Jonathan,

i was as well thinking to do the same.. Can u as well tell me if i have Multiple Date Column in both Table Say

Example.. in Consumption Table

Created Booking Date,

Booking Date,

And in Order Table ,

Order Date,

Closing Date

So if i m Concatenating So now Problem will be there.. as I m Using Created Booking Date as Date for Master Calendar.

Sorry i m new in qlik. so i dont have a Much knowledge.. if u can help me

Re: Need a help on multiple Synthetic Key

tyagishaila — Tue, 07 Jul 2015 08:44:18 GMT

Hi Brijesh,

You can make Composite Key to associate both tables and rename same name fields to avoid synthetic key.

e.g.

TAB1:

Load

A+B+C as key //Composite Key

From....

TAB2:

Load

A as Anew, // Rename Field

B as Bnew,

C as Cnew,

A+B+C as key

From.....

Now tables/charts will associate on the basis of A,B and C.

hope it will help you.

Re: Need a help on multiple Synthetic Key

Tue, 07 Jul 2015 09:07:17 GMT

so u have multiple synkeys in data model means u have more common fields between the tables.....then if u want avoid... u can use Qualify statement....