Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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
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
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
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
Hi Marcus,
I think it won't work when there are more the one month missing a cost value.
Thanks..
Yes, it will, as it will carry the value from the last populated record
You have the solution into the document http://community.qlik.com/docs/DOC-5698
Specific chapter on peek() or previous()
Fabrice
Hi Manish,
Attached are the tables.
Please note that I'm using a free PE.
Thanks for your kind help.