topic Define Variable based on week in QlikView

Define Variable based on week

Thu, 21 Apr 2011 12:31:42 GMT

Hi I have this problem with the loop.

I am looping with the weeks and my loop is for 201115[vCurrweek] to 201118($(vCurWeek)+3)

If the "week" Field in Temp_table is same as $(Vcurrweek) I want to save the stock as variable Stock.

I dont know how to write this

I tried

Let Stock = Peek('WeeklyStock');

But it does not give me the Stockvalue when week is 'i'


SET CV= '0';

 For i= $(vCurWeek) to ($(vCurWeek)+3)

 Temp_Table:
 Load
 SID_0CALWEEK as Week,
 Material as Material,
 SID_CMATLOC as [Location],
 [Weekly Material Stock] as WeeklyStock,
 [Weekly Material Demand] as WeeklyDemand
 Resident FactTable
 ;

 if($(i) = Fieldvalue(Week)) then
 Let Stock = Fieldvalue('WeeklyStock');
 Endif

 If($(Stock) <= 0) then $(CV) else
 exit script()
 End If
........

Thanks

sravan

Define Variable based on week

Thu, 21 Apr 2011 13:50:48 GMT

No Ideas???

Define Variable based on week

erichshiino — Tue, 26 Apr 2011 02:41:18 GMT

I'm curious about this part:

if($(i) = Fieldvalue(Week)) then
 Let Stock = Fieldvalue('WeeklyStock');
 Endif

Does the script go inside the if-case? You can use TRACE to check if the if-condition was ok...
 if($(i) = Fieldvalue(Week)) then
TRACE IS OK;

 Let Stock = Fieldvalue('WeeklyStock');
 Endif

You are using the FieldValue in two different ways. On the first line of your code, try:
 Fieldvalue('Week')

Is week a numeric field to be compared with $i)?

Rgds,

Define Variable based on week

Wed, 27 Apr 2011 12:12:56 GMT

edit: Too big and confusing

Define Variable based on week

Wed, 27 Apr 2011 13:41:49 GMT

I think I am doing some silly Mistake...

when I comment the whole "j" loop, "i" loop works correctly and also Stock variable is correct.

If I test for one "i" value, the whole "j" loop and "i" loop works good.

I wonder why after one i value and loop of J values it does not work for second i value in the loop!

Define Variable based on week

Wed, 27 Apr 2011 16:57:08 GMT

Removed too big and confusing

Define Variable based on week

erichshiino — Wed, 27 Apr 2011 20:31:05 GMT

You are already using

Trace $(Stock);
try to use trace $(i); and trace $(j) and trace $(vFieldValue2); and trace $(vFieldValue_2) to gather more information about what is going on

Define Variable based on week

Thu, 28 Apr 2011 18:19:55 GMT

Edit

Too complicated hence added a simple example later

Define Variable based on week

Fri, 29 Apr 2011 12:58:38 GMT

Hey Erich,

I finally got it work but still have 2 questions. May be you can help me. The problem was between the concatenate and Peek expressions. Somehow peek does not work with concatenate for the tables t1 and t2 (I replaced with Noconcatenate and it works but generates lot of t tables)

When I keep noconcatenate load for t1 and t2 tables, it works but I get a lot of "t" tables. I want to do this calculation of cover for a lot of Materials by looping with each material and I see the problem that my Datamodel will be full of unlimited "t" tables.

First Question:

I need to get the CV values( defined as CV variable) in this manner within the table.

From the above table, the calculation which I made in Qlikview is based on this WordDocument

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0763.COVERAGE-Calculation.docx:550:0]

I get the values CV in my script as a Variable and I need to somehow save them as Field "Cover" in the table "Temp_Table". How??

Second Question:

How to get the t tables reduced in the script? I actually needed only Coverage values and hence these loops. Otherwise I dont need the t tables.

I am attaching a simple example for the figure above showing the values.

Hope you can help me.

Thanks and regards

Sravan

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/StockReportingExample1.qvw]