Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

munna1317
Contributor II

Transactions table issue

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

1 Solution

Accepted Solutions

Re: Loading Historical data into Transactions table issue

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:

Create Periods from TX thread214315.jpg

See attachment

Peter

10 Replies

Re: Loading Historical data into Transactions table issue

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?

johngouws
Contributor II

Re: Loading Historical data into Transactions table issue

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.

munna1317
Contributor II

Re: Loading Historical data into Transactions table issue

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

munna1317
Contributor II

Re: Loading Historical data into Transactions table issue

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

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

munna1317
Contributor II

Re: Loading Historical data into Transactions table issue

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

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

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

munna1317
Contributor II

Re: Loading Historical data into Transactions table issue

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