topic Re: how to handle weekly changing sheets in QlikView

how to handle weekly changing sheets

Tue, 01 Apr 2014 15:30:27 GMT

Hi Everyone

I have excel spreasheet that calculates numbers based on weekly input of rates. This then gets fed into dashboard manually.

I am trying to automate the process, I am trying to figure out the best approach of how to do this, where rates are entered in weekly basis and that will automatically feed the numbers to dashboard.

currently it is laid out like below

Week #: 2

Rates: $$$$

based on above characteristics, excel sheet will go to calendar dates that lies in week 2 and do the calculation applying that rate for entire week2. after which user takes the data and updates Access db, which then feeds into qlikview, cycle happens every week.

Whats the best of automating this. how do you handle weekly changing stuff in qlikview.

Re: how to handle weekly changing sheets

MK_QSL — Tue, 01 Apr 2014 15:36:16 GMT

can you post some sample data along with your requirements?

Re: how to handle weekly changing sheets

its_anandrjs — Tue, 01 Apr 2014 15:41:06 GMT

You can directly read this excel file where you maintain week rates with value after making this excel. Read this excel in separate qvw application and make QVD of this and then read this qvd in your data model. In this step you have to updated only excel file and schedular automatically reads your file and make qvd.

Eg:-

1. Excel file -> QVD Generate qvw -> QVD

2. QVD -> YourDatamodel.

3. Update excel file into MS-Access.(if require for future use but you are also creating QVD data i thing no need)

Hope this helps

Re: how to handle weekly changing sheets

Tue, 01 Apr 2014 16:23:56 GMT

So i was thinking along the same line here is my dilemma.

This is how my input database need to be set-up.

User will enter this info on weekly basis
Period No	Week No	Canadian Dollar rate	US Dollar Rate	British Pound Rate
1	1	10%	10%	10%
1	2	20%	20%	20%
1	3	30%	30%	30%
1	4	40%	40%	40%
2	5	50%	50%	50%

Based on that another sheet or access database needs to get populated which will look like this.

Date	Period	Year	Working Week of Year	Candian Rates	US Dollar Rates	British Pound Rates
1/1/2014	1	2014	1	10%	10%	10%
1/2/2014	1	2014	1	10%	10%	10%
1/3/2014	1	2014	1	10%	10%	10%
1/4/2014	1	2014	1	10%	10%	10%
1/5/2014	1	2014	1	10%	10%	10%
1/6/2014	1	2014	2	20%	20%	20%
1/7/2014	1	2014	2	20%	20%	20%
1/8/2014	1	2014	2	20%	20%	20%
1/9/2014	1	2014	2	20%	20%	20%
1/10/2014	1	2014	2	20%	20%	20%
1/11/2014	1	2014	2	20%	20%	20%
1/12/2014	1	2014	2	20%	20%	20%
1/13/2014	1	2014	3	30%	30%	30%
1/14/2014	1	2014	3	30%	30%	30%
1/15/2014	1	2014	3	30%	30%	30%
1/16/2014	1	2014	3	30%	30%	30%
1/17/2014	1	2014	3	30%	30%	30%
1/18/2014	1	2014	3	30%	30%	30%
1/19/2014	1	2014	3	30%	30%	30%
1/20/2014	1	2014	4	40%	40%	40%
1/21/2014	1	2014	4	40%	40%	40%
1/22/2014	1	2014	4	40%	40%	40%
1/23/2014	1	2014	4	40%	40%	40%
1/24/2014	1	2014	4	40%	40%	40%
1/25/2014	1	2014	4	40%	40%	40%
1/26/2014	1	2014	4	40%	40%	40%
1/27/2014	2	2014	5	50%	50%	50%
1/28/2014	2	2014	5	50%	50%	50%
1/29/2014	2	2014	5	50%	50%	50%
1/30/2014	2	2014	5	50%	50%	50%
1/31/2014	2	2014	5	50%	50%	50%
2/1/2014	2	2014	5	50%	50%	50%
2/2/2014	2	2014	5	50%	50%	50%

how do you set it up in access or excel so that when user enter certain rate, it gets populated for matching period and week number all across the dates the period and weeks fall into.

Re: how to handle weekly changing sheets

Tue, 01 Apr 2014 18:03:10 GMT

any clues guys any idea, how to achieve above in excel or access. Access would be ideal.