topic Re: peek with condition in QlikView

peek with condition

Anonymous — Tue, 21 Jul 2015 01:49:14 GMT

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

Re: peek with condition

sunny_talwar — Tue, 21 Jul 2015 02:31:04 GMT

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:

Re: peek with condition

Anonymous — Tue, 21 Jul 2015 09:02:22 GMT

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

Re: peek with condition

Kushal_Chawda — Tue, 21 Jul 2015 09:44:27 GMT

This is what you are looking for?

Please see the attached

Re: peek with condition

sunny_talwar — Tue, 21 Jul 2015 10:07:48 GMT

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

Re: peek with condition

Anonymous — Tue, 21 Jul 2015 11:22:02 GMT

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

Re: peek with condition

sunny_talwar — Tue, 21 Jul 2015 11:24:25 GMT

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

Re: peek with condition

Kushal_Chawda — Tue, 21 Jul 2015 12:07:51 GMT

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

Re: peek with condition

sunny_talwar — Tue, 21 Jul 2015 12:10:39 GMT

Thanks brother