Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Using loops in script

Hi All,

I have 2 tables with monthly costs and monthly income (pic1).

I have a request to display monthly costs per items sold .

For months which costs are not available, I have to use the last recorded cost.

There can be few months missing.

For example , for January 2014 and February 2014, I have to use the cost value from December 2013 (pic2).

I was thinking of using For-Next loops and Peek to go over both tables and create the third.

Is there a better alternative?

Thanks!

Tags (3)
1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

Re: Using loops in script

I don't think a loop is necessary here.

Load your table in itemcode, period order

Then for your monthly cost field use this expression:

If(isnull([monthly cost] and itemcode = peek('itemcode', -1),

peek('NewMonthlyCost', -1),

[monthly cost]) as NewMonthlyCost

10 Replies

Re: Using loops in script

You can use Peek & Previous function in Script to create the desired result.

Another option is to use Above and Below or Before and After function in Straight or Pivot table.

If you can provide sample data, can help you better way.

marcus_malinow
Valued Contributor III

Re: Using loops in script

I don't think a loop is necessary here.

Load your table in itemcode, period order

Then for your monthly cost field use this expression:

If(isnull([monthly cost] and itemcode = peek('itemcode', -1),

peek('NewMonthlyCost', -1),

[monthly cost]) as NewMonthlyCost

MVP
MVP

Re: Using loops in script

You can use For / Next loops in a QlikView load script - and I will often enumerate around source files in a folder or rows in an existing table.

There are neater ways of achieving what you are after though.  The best explanation I have seen of this is by Henric Cronström in his blog posting: http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

The reference to the WHILE statement in the comments is also very useful - you can build a loop right into your load statement in a preceding load.

Hope that helps.

Steve

Not applicable

Re: Using loops in script

Well, I wouldn't use any For-Next Loop. I would suggest the below approach -

Costs_temp:

Load Month

From Invoices   //Assuming your Invoice table would have all the months

Left Join (Cost_Temp)

Load Month,

       Cost1

From Costs;

Costs:

Load *

       if(isnull(Cost1)<0, peek(Cost1), Cost1) as Cost

Resident Costs_temp

Order by month

The idea would be to populate preceding month's Cost in case current month's cost is NULL.

Hope this is of help.

Regards,

SK


Not applicable

Re: Using loops in script

I would say:

in the script

JOIN the two tables

Sort by Dates

Use peek  (as shown above, test that the item code is the same as the one of the previous line)

Fabrice

Not applicable

Re: Using loops in script

Hi Marcus,

I think it won't work when there are more the one month missing a cost value.

Thanks..

marcus_malinow
Valued Contributor III

Re: Using loops in script

Yes, it will, as it will carry the value from the last populated record

Not applicable

Re: Using loops in script

You have the solution into the document http://community.qlik.com/docs/DOC-5698

Specific chapter on peek() or previous()

Fabrice

Not applicable

Re: Re: Using loops in script

Hi Manish,

Attached are the tables.

Please note that I'm using  a free PE.

Thanks for your kind help.

Community Browser