peek with condition

[Anonymous]

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

[sunny_talwar]

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:

[Anonymous]

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

[Kushal_Chawda · Accepted solution]

This is what you are looking for?

Please see the attached

[sunny_talwar]

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

[Anonymous]

sunindia‌ Kush141087‌ EXCTLY SIR...  Thanks a ton

[sunny_talwar]

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

[Kushal_Chawda]

sunindia‌ I really like your honesty and appreciate it. Nice to have people like you on community

[sunny_talwar]

Thanks brother