Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need a help on multiple Synthetic Key

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

11 Replies
krishna20
Specialist II
Specialist II

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

Not applicable
Author

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

Not applicable
Author

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
Creator III
Creator III

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

Not applicable
Author

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

Not applicable
Author

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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