topic Re: Data model help question in QlikView

Data model help question

Mon, 26 May 2014 14:01:55 GMT

If i have a data model as per below and i need to do join. How would i avoid sythetic key.

Master Customer

Master Account No

Master Account Name

Customer_Info:

Sub account

Master Account No

Name

Contact

Expiree Info Temp:

Sub Account

Year Exp

Month Exp

Day Exp

End result. I need to merge Expiree Info field into Master Customer. In order to do so I have to:

Left Join Expiree Info Temp with Customer_info. Drop table Expiree_info_Temp.
Left Join Customer_Info (Expiree Info Temp related Fields) into Master Customer.

However when i do this I will have below fields duplicated on 2 tables creating syc key.

Year Exp

Month Exp

Day Exp

How can i avoid that. 2 ways i can think of is:

Rename field when merging it with Master Customer. however this is dupe of fields.
Do a final resident load of Customer_info and drop those fields

I wonder if there is a better way to avoid this.

Re: Data model help question

Mon, 26 May 2014 14:04:49 GMT

Hi MD,

may you paste your script?

reg

Re: Data model help question

Anonymous — Mon, 26 May 2014 14:05:03 GMT

Qualify those three fields

Re: Data model help question

eduardo_sommer — Mon, 26 May 2014 14:09:03 GMT

After the join, you can drop the entire tables (Expiree_info_Temp and Customer_Info), unless you have records in the right tables with no match in the left tables and you need them.

Re: Data model help question

MayilVahanan — Mon, 26 May 2014 14:10:27 GMT

After join with master table, drop fields (Year Exp,Month Exp,Day Exp) from "Customer_Info" table.

So you can remove synthetic key.

Re: Data model help question

PradeepReddy — Mon, 26 May 2014 14:11:03 GMT

If we are doing the Left Join, synthatic keys won't be formed. I have tried to do what you did.

Master_Customer:

LOAD [Master Account No],
[Master Account Name]
FROM

Account.xlsx
(ooxml, embedded labels, table is [Master Customer]);

Left Join(Master_Customer)
Customer_Info:
LOAD [Sub account],
     [Master Account No],
     Name,
     Contact
FROM
Account.xlsx
(ooxml, embedded labels, table is Customer_Info);

Left Join(Master_Customer)
Expiree_Info_Temp:
LOAD [Sub Account],
     [Year Exp],
     [Month Exp],
     [Day Exp]
FROM
Account.xlsx
(ooxml, embedded labels, table is [Expiree Info Temp]);

Regards,

Pradeep

Re: Data model help question

Mon, 26 May 2014 14:11:57 GMT

and why don't you keepo your model as it is ?

If you want to join 2 tables why do you keep both after ?

best regards

Chris

Re: Data model help question

Mon, 26 May 2014 14:34:27 GMT

I think you can go with QUALIFY statement...