topic Re: Loading Historical data into Transactions table issue in QlikView

Transactions table issue

munna1317 — Sun, 24 Apr 2016 02:19:09 GMT

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

Re: Loading Historical data into Transactions table issue

sunny_talwar — Sun, 24 Apr 2016 02:42:43 GMT

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?

Re: Loading Historical data into Transactions table issue

johngouws — Sun, 24 Apr 2016 04:26:01 GMT

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.

Re: Loading Historical data into Transactions table issue

munna1317 — Mon, 25 Apr 2016 04:39:13 GMT

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

Re: Loading Historical data into Transactions table issue

munna1317 — Mon, 25 Apr 2016 04:59:03 GMT

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

Re: Loading Historical data into Transactions table issue

Peter_Cammaert — Mon, 25 Apr 2016 09:53:04 GMT

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

Re: Loading Historical data into Transactions table issue

munna1317 — Mon, 25 Apr 2016 11:10:31 GMT

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

Re: Loading Historical data into Transactions table issue

Peter_Cammaert — Mon, 25 Apr 2016 12:33:58 GMT

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:

For each employee you want to calculate the periods during which the modified details are in effect, right?
Each period starts with the EFFECTIVE_DATE value of the current record (=From date), but how do you calculate the To Date? Shouldn't it be the From Date of the next records minus one day, instead of begin equal to the From date of the next record? Two sets of values cannot be in effect on the same date, can they?
Where does the last period end? With todays date?

Best,

Peter

Re: Loading Historical data into Transactions table issue

Peter_Cammaert — Mon, 25 Apr 2016 12:37:37 GMT

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

Re: Loading Historical data into Transactions table issue

munna1317 — Mon, 25 Apr 2016 13:11:54 GMT

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

Re: Loading Historical data into Transactions table issue

Peter_Cammaert — Mon, 25 Apr 2016 14:07:27 GMT

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