Need a help on multiple Synthetic Key

[#1]

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

[krishna20]

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

[#3]

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

[#4]

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.

[marksmunich]

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

[#6]

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. just keep one common feild and give remaing feilds alias names

[#7]

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.

[MayilVahanan]

Hi

Try like below

Create link table between these table

or

Create the Composite key and link with that key.

[jonathandienst]

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.

[#10]

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