
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
peek with condition
Hi all
I having table with material ,monthname and unit and rates I had generated the missing data using autogenrate
Now I want rates to be filled with the rates of its previous month where the rate of the material is available but the rates should be of respective unit and material ..i had tried using peek function bt couldnt get rid of null values yet pls suggest
Thanking You
Vinayagam
- Tags:
- new_to_qlikview
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this may be:
Table:
LOAD UNIT,
MATERIAL,
MonthName(Date#(MONTH, 'MMM YYYY')) as MonthYear,
RATE
FROM
Community_173305.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD UNIT,
MATERIAL,
MonthYear,
RATE,
If(Peek('UNIT') = UNIT and Peek('MATERIAL') = MATERIAL, ALT(PEEK('NEWRATE'), RATE), RATE) as NEWRATE
Resident Table
Order By UNIT, MATERIAL, MonthYear;
Output:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sir
Thanks for the valuable sugeestion but as per my requirement the value of MAT1 for the unit A1 from november onwards should be 21.6 since there is rate value
THanking You
Vinayagam

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is what you are looking for?
Please see the attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I see what you mean.
Try this script:
Table:
LOAD UNIT,
MATERIAL,
MonthName(Date#(MONTH, 'MMM YYYY')) as MonthYear,
RATE
FROM
Community_173305.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD UNIT,
MATERIAL,
MonthYear,
RATE,
If(Peek('UNIT') = UNIT and Peek('MATERIAL') = MATERIAL and Len(RATE) = 0, ALT(PEEK('NEWRATE'), RATE), RATE) as NEWRATE
Resident Table
Order By UNIT, MATERIAL, MonthYear;
DROP Table Table;
Output

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sunindia Kush141087 EXCTLY SIR... Thanks a ton

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Awesome
I would ask you to close this thread by marking the correct answer and any helpful answers. Since Kush answered this ahead of me, he deserves to get correct answer.
Best,
Sunny

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sunindia I really like your honesty and appreciate it. Nice to have people like you on community

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks brother
