topic Re: Using loops in script in QlikView

Using loops in script

Fri, 14 Mar 2014 12:27:30 GMT

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!

Re: Using loops in script

MK_QSL — Fri, 14 Mar 2014 12:32:39 GMT

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.

Re: Using loops in script

marcus_malinow — Fri, 14 Mar 2014 12:34:03 GMT

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

Re: Using loops in script

stevedark — Fri, 14 Mar 2014 12:34:03 GMT

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

Re: Using loops in script

Fri, 14 Mar 2014 12:39:34 GMT

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,

Re: Using loops in script

Fri, 14 Mar 2014 12:40:59 GMT

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

Re: Using loops in script

Fri, 14 Mar 2014 12:40:59 GMT

Hi Marcus,

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

Thanks..

Re: Using loops in script

marcus_malinow — Fri, 14 Mar 2014 12:43:06 GMT

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

Re: Using loops in script

Fri, 14 Mar 2014 12:47:20 GMT

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

Specific chapter on peek() or previous()

Fabrice

Re: Re: Using loops in script

Fri, 14 Mar 2014 12:47:47 GMT

Hi Manish,

Attached are the tables.

Please note that I'm using a free PE.

Thanks for your kind help.

Re: Using loops in script

Fri, 14 Mar 2014 13:24:47 GMT

Thank you all!

I find the references very useful too!