Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Eveyone,
I have a transaction table(Employee details) ,which contains Effective date,i have created a calendar in Transaction table .
History table contains Effective From and Effective To dates,I need to include history table changes to Transaction table.
History table Contains all the fields same as in Transaction table.
Any Suggestions would be greatly appreciated.
Please find the attached sample data for reference.
Regards,
A.Harish
I'm still not sure that I understand what you plan to do with the calendar.
This one creates From/To periods from both your transactions and your historical records (example files from OP). The result is:
See attachment
Peter
If all the fields are common between the two tables, what is the point of joining them? Not sure what you are trying to do?
Hi there,
If these tables are both transaction tables, and are exactly the same, they should automatically "union".
If these tables are both transaction tables, and there is a difference in field names and renaming a field is not an option, you can use "concatenate" between the 2 LOAD statements.
If one table is a"transaction" table and the other is a "header" table you should make a join.
Cheers.
Hi Sunny,
Fields are same apart from Effective To date (HISTORY table).History table contains all the changes where as transaction table contains latest change.
For example ,Employee Department changed twice then in History table will have 2 records where as in Transaction table which contains the latest change.
Regards,
A.Harish
Hi John,
I used Concatenate Statement but year ,Month fileds data are coming as Null for History data.
I have created Calendar in Transaction table.
I want to create all the dates,Month and year for History table data,For each employee there are multiples changes.
Thanks in Advance.
Regards,
A.Harish
If you created Year and Month fields when loading the Transaction table, do the same when adding the data from the History table. Select the proper date field in this table, and calculate calendar fields in the CONCATENATE LOAD.
A calendar is usually created as a separate table, and only AFTER all your facts are loaded. Otherwise you may end up with an incomplete calendar.
Can you post an example document?
Peter
Hi Peter,
As you said I should Create a calendar for History table as well,I am facing issue in Creating a calendar for History data.
Please find the attached data.
Any Suggestions would be greatly appreciated.
Thanks
A.Harish
Unfortunately, your Sample Data file doesn't use the Transaciton/History records from your earlier post. Therefor, we cannot easily deduce from the data how the From/To-dates are calculated.
By lack of usable examples, let me ask you a few questions:
Best,
Peter
Usually, a calendar in QlikView is a continuous range of dates from a starting date (the earliest one in your facts) until a predetermined end date or the last date in your facts. Are you going to use INTERVALMATCH to map each employee period on this continuous timeline? Or what do you plan to do with all these From/To periods?
Peter
Hi Peter,
As u mentioned,Each period starts with the EFFECTIVE_DATE value of the current record (=From date), From Date of the next records minus one day,(=Todate).
Qlikview creates dates from Starting to end date.But in actual data, Each employee has multiple changes For(ex:Emp A has Department Change in Jan -16 as D1 and Again Feb -16 as D3) if i Create a Calendar it is taking only One Department change data From standing to End .
So need in help in Creation of dates with Department changes.
Regards,
A.Harish