Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

1 Solution

Accepted Solutions
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

10 Replies
MK_QSL
MVP
MVP

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
Partner - Specialist III
Partner - Specialist III

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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
Author

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
Author

Hi Marcus,

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

Thanks..

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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

Specific chapter on peek() or previous()

Fabrice

Not applicable
Author

Hi Manish,

Attached are the tables.

Please note that I'm using  a free PE.

Thanks for your kind help.