Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
munna1317
Creator II
Creator 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
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

10 Replies
sunny_talwar

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
Partner - Specialist
Partner - Specialist

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II
Author

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