topic Re: Create LinkTable for creating MasterCalendar in QlikView

Create LinkTable for creating MasterCalendar

jjordaan — Tue, 14 Aug 2012 14:41:06 GMT

Hello QlikView lovers,

I am working on a QV app to create a data model.
I try wherever possible to work towards a Star model. So I created a facttable called 'Transactions' (Sales Lines, Purchase Lines).
The original sales lines contain 2 date fields and the original purchase lines 4.
Through a Link Table I want to place alle the date fields into the LinkTable so I can then created a MasterCalender out of the LinkTable.
I only got $ Syn keys because all the fields are from the same table wich I created through the concatenate action.

I'm totally stuck with this problem

How do I fix this?

As an attachment I attached an example QVW and the 2 QVD's wich contains the data.

Thanks for the help.

Re: Create LinkTable for creating MasterCalendar

pgalvezt — Tue, 14 Aug 2012 16:16:16 GMT

If What you want is eliminate the Sys Keys you need to use a Mapping Function.

Hope Help you.

Re: Create LinkTable for creating MasterCalendar

jjordaan — Wed, 15 Aug 2012 07:04:47 GMT

pgalvezt,

Thanks for your help.

Last night (I didn't saw your post till this morning) I created a different model.

I don't get any Sys Keys anymore but can you tell me if this is the best way to create a calender if I want to use multiple date fields from the same (concatenated) table.

I added an example as a attachment.

Thanks for your time again.

Re: Create LinkTable for creating MasterCalendar

pgalvezt — Thu, 16 Aug 2012 14:30:16 GMT

There is a CookBook in QlikView one of the models is called master calender take a look is very usefull. Now you can check your model using your data and compare with report results.

Re: Create LinkTable for creating MasterCalendar

jjordaan — Thu, 16 Aug 2012 15:30:41 GMT

pgalvezt,

I can't find the MasterCalendar in the cookbook?

Re: Create LinkTable for creating MasterCalendar

pgalvezt — Thu, 16 Aug 2012 16:07:32 GMT

I atttached a calendar Master.

Hope Help you.

Re: Create LinkTable for creating MasterCalendar

Thu, 16 Aug 2012 18:42:56 GMT

My understaning of Master Calender is connect the rest of the dates from different tables to MC using alias right. For instance I have multiple dates from various tables and when i was trying to connect to MC, end up with synthic keys.

I have modified your MC script, have look at it and see where am i making mistake. Other words please let me know what is the best way to handle this situeation.

thnaks for your help.

Re: Create LinkTable for creating MasterCalendar

jjordaan — Fri, 17 Aug 2012 09:57:04 GMT

Pgalvezt,

First off all thank you for your reaction.

My question is not how I should make a calendar but more of how can I create a Master Calendar of multiple dates from the same (concatenated transaction)? I think that I have to create a linked table. I would like to know if the linked table that I made in the example is the right way to create a linked table by using multiple date fields of the same table and then uses the linked table to create a Master Calendar.

Re: Create LinkTable for creating MasterCalendar

jjordaan — Fri, 17 Aug 2012 11:40:22 GMT

Hi PurnaC10,

I modified your qvw.

Take a look at it.

I hope it will help you.

If you have any questions don't hasitate to ask.

Re: Create LinkTable for creating MasterCalendar

Fri, 17 Aug 2012 15:28:29 GMT

Thanks for sharing your script with us. man you have answered my question and im going throght your modifed script and let you know if i have any questions.

Thank you very much for your time.

Re: Create LinkTable for creating MasterCalendar

pgalvezt — Fri, 17 Aug 2012 15:37:31 GMT

Hi, Could you please mark as Answer correct so the others can consult this problem.

Thanks

Re: Create LinkTable for creating MasterCalendar

jjordaan — Fri, 17 Aug 2012 15:40:07 GMT

My question is not answered.

I hope I could answered the question of PurnaC10.

Re: Create LinkTable for creating MasterCalendar

Fri, 17 Aug 2012 16:27:14 GMT

Sorry for the confusion. You have answerd my question.

Re: Create LinkTable for creating MasterCalendar

Tue, 25 Sep 2012 12:52:27 GMT

I'm able to create master calender, but how do i create master calender with hours and mins granularity. I tried to pass date-hours-mins-sec to master calnder script but it failing with File Not Found error. Otherwise it works fine with only date value.

How do i create a MC include hours and mins.

Re: Create LinkTable for creating MasterCalendar

jjordaan — Wed, 26 Sep 2012 07:27:14 GMT

PurnacC10,

Can you attach an example?

If date field in the database is a DateTime field everything should go fine.

In the Mastercalendar you can add a field like DateTime or something and the you have the datetime.

In the Mastercalendar you also extract the year, month and so on. With that fields you don't have an timestamp but is abvious because you extract onley the year or month.

Re: Create LinkTable for creating MasterCalendar

Wed, 26 Sep 2012 11:42:29 GMT

I'm uploading the QVD. you can see a note on Data01 column format. Please let me know if you have any questions.

Thanks.

Re: Create LinkTable for creating MasterCalendar

jjordaan — Wed, 26 Sep 2012 19:27:21 GMT

Is the script running in SQL Server?

I think it won't because of the DateTime function?

Re: Create LinkTable for creating MasterCalendar

Thu, 27 Sep 2012 12:22:32 GMT

It is in Oracle. ie. Oracle is my data source.