Wrong Approach - Synthetic Key Forming.

[#1]

Hi All

I need best approach to handle my case. As I am a newbie so I am unaware of the Data Modelling and Best Practices concepts.

I have Attached my Application. Kindly provide the Best Approach and Solution.

Regards

Eric

[krishnacbe]

Hi,

To avoid synthetic keys you can use either choices below

1. Concatenate the tables into single table

2. Join the tables with Linked table

3. Concatenated Keys can be created

4. Qualify the tables.

5. Use Alias

[ramoncova06]

join the data from headerfile and data tabs together then concatenate the car, truck and bike data together, while concatenating  add a new field called fact source, and name with whatever data you are appending, that will make it easier to identify later on

LOAD ID AS Common_ID,

     Data_Code

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is HeaderFile);

left join

LOAD Data_Code,

     Type_Data

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is Data);

Fact:

LOAD Car_Date AS Common_Key, 

     Value1,

     Car_ID AS Common_ID,

     1 as FactType

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is Car);

Concatenate(Fact)

LOAD Truck_Date AS Common_Key,

     Value2 as Value1,

     Zone,

     Truck_ID AS Common_ID,

     2 as FactType

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is Truck);

Concatenate(Fact)

LOAD Bike_Date AS Common_Key,

     Value3 as Value1,

     Source,

     Bike_ID AS Common_ID,

     3 as FactType

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is Bike);

load * Inline

[FactType,Fact

1,Car

2,Truck

3,Bike];

EXIT Script;

[#4]

kindly help

stalwar1‌

‌

[sunny_talwar]

Did the solution offered by Ramon‌ not providing you the result you would want? May be if you can point out what's not working with his solution, we can all look for ways to fine tune it

NOTE: I have not looked at his response in depth.

[#6]

I have just shared the sample data.

I my scenario, I have number of fields in each different and all are different.

So, can I concatenate Data.

As per the concatenate(), if you do concatenate between 2 tables with different fields it will Add Data.

There is no problem with concatenate, if you have same fields and using Distinct.

That's why I am afraid to use Concatenate.

Sir, Correct me if I am wrong.

[sunny_talwar]

Eric Paul wrote:

As per the concatenate(), if you do concatenate between 2 tables with different fields it will Add Data.

There is no problem with concatenate, if you have same fields and using Distinct.

I am not sure I understand your concern here. But an alternative to concatenation is to create a linktable

Concatenate vs Link Table

[Anonymous]

link table concept is useful to you try that way

may be this link useful to you

Link Table in QlikView – Learn QlikView

[#9]

I have a different Scenario.

Sir, kindly help and provide a solution.

[#10]

Sir I am not getting expected Result.

Help me Out. I got Stuck.