topic Re: QVD help in QlikView

QVD help

Thu, 16 Oct 2014 14:38:51 GMT

Experts, I need your help.

I have historical and future forecast data that I want to show in a graph. Right now, I have two QVDs: one that shows historical and one that shows future. Each QVD has the following information: Snapshot week, calendar week, Product, Region, Brand, and Forecast 1, Forecast 2 and Forecast 3.

It can be a little confusing because in the historical forecast qvd, the snapshot week and the calendar week is switched. See my example. The problem that I'm having is that When I join these two tables, my current week forecast gets doubled. Instead of 600, 630 and 660 for forecast 1, forecast 2, and forecast 3 for the week of 10/06/2014, I'm getting 1200, 1260, 1320.

What is the best way to avoid this double? in the graph? in the script? and How do I avoid it? I have attached a QVQ that best shows this example. Please help me as this is becoming very urgent.

Re: QVD help

Anonymous — Thu, 16 Oct 2014 14:43:22 GMT

I would solve it in script. Just load your both tables in a same one, with a field indicating if forecast or historic. Like this:

table: load *, 'H' as datatype FROM yourhistoricalsource;

concatenate load *, 'F' as datatype FROM yourforecastsource;

Then in back end use datatype field to select your data.

Hope it helps.

Marc.

Re: QVD help

Thu, 16 Oct 2014 14:45:40 GMT

marc, thank you for your suggestion.

unfortunately, i'm pretty new to qlikview but i'm not sure how your suggestion would help fix the double issue? would the concatenate function get rid of the double 10/06 data?

Re: QVD help

Anonymous — Thu, 16 Oct 2014 14:51:01 GMT

ok, then try with:

table:

LOAD*,

'H' as datatype

FROM yourhistoricalsource;

concatenate

LOAD

snapshot_week as calendar_week,

calendar_week as snapshot_week ,

product, region, brand, forecast_1, forecast_2, forecast_3,

'F' as datatype

FROM yourforecastsource;

Re: Re: QVD help

Thu, 16 Oct 2014 14:59:09 GMT

Marc,

the concatenation still did not help with my doubling issue. please see the picture attached

Re: QVD help

Anonymous — Thu, 16 Oct 2014 15:15:24 GMT

i would try one of these 2 options.

A) add the dimension datatype to your actual sales table. So you will distinguish the amounts of historical/future forecast.

B) Use set analysis in the expressions, for instance, if you wold like to show only the future forecast values, write the expression: =sum({<datatype={'F'}>} forecast_1) instead of =sum(forecast_1)

Let me know.

Marc.

Re: Re: QVD help

Thu, 16 Oct 2014 15:23:21 GMT

I think I figured out a way to do this in the QVD itself. In the historical QVD, I have it set up where it picks up any snapshot weeks greater than current week-10

I need to say pick up everything less than currentweek-1 and everything greater than currentweek-10

Can anyone help me with this SQL select statement to do what I said above in the bold?

This is my current script:

SQL Select * from Forecast Table

Where snapshot_week >= $(vMinWeek) > and calendar_week = $(vCurrWeek);

STORE * FROM Forecast INTO $(vRootPath)\Data\QVDs\Historical_10week.QVD;

Re: QVD help

Anonymous — Thu, 16 Oct 2014 15:34:44 GMT

Change your where clause to:

Where snapshot_week >= $(vMinWeek) > and calendar_week >= $(=vCurrWeek-1) and calendar_week <= $(=vCurrWeek-10);

Also, I would recommend instead of using a single datatype field for 'F', 'H' create two additional fields.

pseudo code:

If Historical then 1 as HistoryFlag

If Current then 1 as CurrentFlag.

Then you can use these flags to access your data by multiplying the field you want by them:

Sum(Amount*Historyflag) will sum only those records with HistoryFlag set to 1.

Re: QVD help

d_pranskus — Thu, 16 Oct 2014 16:09:32 GMT

Could attached file help in achieving the solution. I think the problem is related to the fact that for 10/6/2014 both future and historical forecast exists. If you need to load future forecast for the date just switch historical and future tables.

Cheers

Re: Re: QVD help

Thu, 16 Oct 2014 18:03:09 GMT

Changing the 'where' clause definitely helped.

If I could ask one more favor, I need to make another graph that is static. I need to be able to see from current week, 2 weeks of actual sales and historical forecast and 4 weeks of future forecast. In my example,

Current week in the application is 10/6/2014.

I need to see sales and historical forecast for: 9/29/2014, 9/22/2014

I need to see future forecast for: 10/6/2014, 10/13/2014, 10/20/2014, 10/27/2014

How can I make this happen? I will attach the QVW if anyone can help, I would greatly appreciate.