topic Re: question about QVDs in QlikView

question about QVDs

Thu, 04 Sep 2014 14:40:23 GMT

I have a QVD that is refreshed daily.

Example: I have a table with the following fields in a QVD

Week, Product, Plant, Planned Production Weekly, Planned Production Daily

I only want it to change 'Planned Production Weekly' field if it is a Monday.... Is it possible for ONE field in a QVD to be updated ONLY if it is a certain day of the week?

Re: question about QVDs

MK_QSL — Thu, 04 Sep 2014 14:45:57 GMT

What you want to do? Not clear from your question itself...

Re: question about QVDs

simondachstr — Thu, 04 Sep 2014 14:46:03 GMT

1. Load the QVD

2. Drop the field Planned Production Weekly

3. Do a left join with the new 'Planned Production Weekly' values.

4. Store QVD

Depending on your requirements you might need to alter step 2. and 3.

Re: question about QVDs

jvitantonio — Thu, 04 Sep 2014 14:48:21 GMT

Hi,

I'd like to understand what you are trying to achieve before I give my opinion.

To answer your question, yes. It's possible. But why would you do this? Each Monday your Planned Weekly field will be updated for the WHOLE qvd file unless you have something in place.

Can you please describe your architecture?

Re: question about QVDs

JonnyPoole — Thu, 04 Sep 2014 14:48:36 GMT

You can do that . You would join the new data from the source to your old QVD on week, product, and plant. Then overwrite the existing QVD with the joined data. IT all starts with an IF to check todays weekday:

------------------------------------------------------

let vTodaysWeekDay = WeekDay(Today());

if vTodaysWeekDay = 'Mon' then

NewData:

Load

Week, Product, Plant, Planned Production Weekly

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

ExistingData:

Load

Week, Product, Plant, Planned Production Daily

FROM <YourQVD>

left join (ExistingData)

load

resident NewData;

drop table NewData;

store ExistingData as <QVD> (QVD);

end if;

Re: question about QVDs

Thu, 04 Sep 2014 15:42:04 GMT

My reason for doing this is that right now, I do not have a 'Planned Production Weekly' field in my QVD, only 'Planned Production Daily'

My file gets overwritten everyday...so I lose my original Monday value. I want to show my Monday value in a chart so that on Friday, I can see how much it has changed from Monday...

Re: Re: question about QVDs

jvitantonio — Thu, 04 Sep 2014 16:33:31 GMT

Hi,

Isn't easier to aggregate your values by week?

In your chart, you add Plant and Product as Dimension, and as expression you use:

sum({< Date = {$(=weekstart(Date)+5)}>} Planned)  - sum({< Date = {$(=weekstart(Date))}>} Planned)

Re: question about QVDs

JonnyPoole — Thu, 04 Sep 2014 16:42:30 GMT

So to do the regular load on other days just add an 'elsef' clause right above the 'end if'

ie:

else

<Regular load statement> ;

end if;