Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
My response was a summary of the steps you need to follow. Can you post the data model you have now?
Could
you post sample?
Robert,
Thanks for the fast reply, below is a picture of the Table View.
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
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?
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.
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?
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
I agree, however, when I remove these fields, my charts change drastically.
My response was a summary of the steps you need to follow. Can you post the data model you have now?