Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Removing Synthetic Keys and Data Consistancy

I'm working on cleansing an array of data that I have parsed out based on each cross-functional division, for example, I have an individual Excel file for Finance, HR, Sales, the list goes on ....

I have found it easy, within the aforementioned Excel files, to maintain the same headings for each division, such as Metric, Division, Date, Plan, Actuals, etc. However, once I import these flat-files into QlikView, synthetic keys are created for each table forthright.

Questions:

1) Is it possible for me to a single LOAD INLINE statement in which I define all of the identical fields within each file, and then CONCATENATE them all to this hardcoded fact table? If not, does anyone have any other suggestions to implement in order to keep my Excel naming standardized, while keeping my script as efficient as possible?

2) If I were to create this LOAD INLINE statement, I eventually need to rename my DATE to MONTH(DATE) as Month and YEAR(DATE) as Year. Should this also be defined in the LOAD INLINE statement?

Thanks in advance for any and all help.

1 Solution

Accepted Solutions
Colin-Albert

My response was a summary of the steps you need to follow. Can you post the data model you have now?

View solution in original post

10 Replies
robert_mika
Master III
Master III

Could

you post sample?

Not applicable
Author

Robert,

Thanks for the fast reply, below is a picture of the Table View.

Example.png

robert_mika
Master III
Master III

There is many way to get to star schema.

You can:

use Year&Month as Key in ISC,Finance and MasterCalendar

Remove Division & 214 JOP from ISCData (assume there are the same as in Finance Data)and the

change

Date as ISCDate

Plan as ISCPlan

etc...

or use Qualify

Do you Qualify?- How to use QUALIFY statement

Not applicable
Author

Yes, I understand that I can rename each field, or use QUALIFY*, etc. However, is it possible for me to LOAD INLINE all the fields in common and CONCATENATE every table to the latter?

Colin-Albert

It is easiest to resolve synthetic keys if you add new tables to the data model one at a time, and resolve any synthetic keys that exist before adding the next table.

Adding an "Exit Script" command is helpful - I often use this in a tab on its own that I can promote or demote through the script when testing.

Not applicable
Author

How to I remove the synthetic key created due to my dates in my individual tables in addition to those assigned in the Master Calendar file?

Colin-Albert

You do not need Month / Year etc. in the ICS or Finance tables, just a single Fact table with dimensions of Division etc., and Date and measures Actual, Forecast. Date will link to the Calendar table has the breakdown into the various date parts

Not applicable
Author

I agree, however, when I remove these fields, my charts change drastically.

Before and After.png

Colin-Albert

My response was a summary of the steps you need to follow. Can you post the data model you have now?