Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Removing Synthetic Keys and Data Consistancy

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

10 Replies

Re: Removing Synthetic Keys and Data Consistancy

Could

you post sample?

Not applicable

Re: Removing Synthetic Keys and Data Consistancy

Robert,

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

Example.png

Re: Removing Synthetic Keys and Data Consistancy

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

Re: Removing Synthetic Keys and Data Consistancy

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?

Re: Removing Synthetic Keys and Data Consistancy

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

Re: Removing Synthetic Keys and Data Consistancy

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?

Re: Removing Synthetic Keys and Data Consistancy

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

Re: Removing Synthetic Keys and Data Consistancy

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

Before and After.png

Re: Removing Synthetic Keys and Data Consistancy

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

Community Browser