topic Re: Calendar Dates & Schema in QlikView

Calendar Dates & Schema

Anonymous — Mon, 15 Feb 2016 21:24:43 GMT

All,

I'm fairly new to Qlikview and working on my first Dashboard. The issue that I'm having is with dates. I've Concatenated three files into my Fact Table that have different date fields. Below are the files, header names and examples of the date data found in them.

Retail File #1:

Retail Date . 01/01/2013, 01/05/2014

Retail Month - 1, 2, 3, ..12

Retail File #2 (different source):

Retail Month - 1, 2, 3, ..12

Retail Year - 2012, 2013, 2014

Target File:

Plan Year - 2012, 2013, 2014, 2015,

Plan Month - 1, 2, 3, ..12

I would like to be able to compare Retails to plan over the same time periods (filtering on 2015 gives me all 2015 retail and 2015 plan). Not sure if I need to create a 'master' date field in a LinkTable? Or, should I split my three files into separate tables (ie. is it a schema issue?) Just having trouble visualizing this since I have some data that has a full date and other data that is just month and year.

Any help would be great, thank you!

Re: Calendar Dates & Schema

Mon, 15 Feb 2016 22:32:52 GMT

Try this in your script

Retail File #1:

Year(Retail Date) as Year,

[Retail Month]

Retail File #2:

[Retail Month],

[Retail Year] as Year

Target File:

[Plan Year] as Year,

[Plan Year],

[Plan Month]

Re: Calendar Dates & Schema

Oleg_Troyansky — Mon, 15 Feb 2016 22:49:59 GMT

If you concatenated your Facts into a single Fact table, then you should also standardize your dates on a single common field.

Ideally, if you could bring all three files into the same level of granularity. It looks like the first file has daily data and the other two files have monthly data. Ask your users - is there any value in keeping the daily data for the first file, if the other two files are monthly. If you can settle on a single level of granularity that would be the easiest.

If you need to keep individual dates for the first file, then you could create a common MonthYear field. Something like this:

MonthStart(RetailDate) as MonthYear - from File 1

...

MakeDate(RetailYear, RetailMonth, 1) as MonthYear - from File 2

...

MakeDate(PlanYear, PlanMonth, 1) as MonthYear - from File 2

Then, all the other fields (Year, Quarter, etc...) that are related to Months, should be associated with the common MonthYear. Then, you will be able to compare Plan to Actual and analyze all three facts together.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Calendar Dates & Schema

Anonymous — Tue, 16 Feb 2016 14:05:40 GMT

Oleg,

Thank you for your response. Should I insert the MonthYear Field after my Load statements for the 3 files? Should I also Concatenate this field to my Fact Table?

Lastly, once I've created the MonthYear Field, can I use that in my MasterCalendar? Thanks.

Re: Calendar Dates & Schema

Anonymous — Tue, 16 Feb 2016 15:52:49 GMT

I think I got this to work. I was renaming my fields in the Load Statement, ex: [Retail Month] as RetailMonth. When I use the unchanged name from the Load file everything works fine.

I’m still stumped on how to change the name of the field and use the name in my MakeDate function. My guess is it would have to come after the statement so that Qlikview knows the Field exists.

What is the proper way to rename the field and than use the Renamed field in the function. Would it come after the load in a Join statement back to the Fact Table? Thanks.

Re: Calendar Dates & Schema

Oleg_Troyansky — Tue, 16 Feb 2016 17:08:16 GMT

Hi,

In my suggestion, the three lines were supposed to be added to the three individual LOAD statements for the three data files. Then, when you concatenate the three sources into a single fact, you'd already have the common field MonthYear populated in all three.

The field names that I used are just there for example, feel free to rename all fields.

Notice that you cannot rename multiple fields into a single field AFTER the data is loaded. You can only rename fields in the process of the data load.

cheers,

Oleg Troyansky

Re: Calendar Dates & Schema

Anonymous — Tue, 16 Feb 2016 17:59:20 GMT

Yes, I am now adding the three Load statements for the three data files directly into the load statements. I think I would need a Preceding Load in this case to change the name of the filed and use the new name as part of the new load statement.

Thanks for all of your help.

Re: Calendar Dates & Schema

Oleg_Troyansky — Tue, 16 Feb 2016 18:34:50 GMT

You can use a preceding load, or you can simply add an extra line to the original load - there is no problem with that.