Generate missing fields with peek() not work

[jarokall]

Hello, I'm trying to generate missing change rates for the future months in years. If the change rate is null, I need to add the rates from the last month.  I try using function peeek() but it seems not to work. Check the attached screenshot with the table. 

Have you please some idea?

I highlighted important parts of the code below..

SORDERQ:
LOAD
SOHNUM_0 as SO_Number,
ITMREF_0 as Item,
SOPLIN_0 as SO_LIN,
Num(QTY_0) as SOQ_QtyOrdered,
Date(("SHIDAT_0"), 'YYYY-MM-DD' ) as SO_ShipDate,
Date(("SDSHIDAT"), 'YYYY-MM-DD' ) as SD_ShipDate,
Year("SHIDAT_0") as Year,
Month("SHIDAT_0") as Month,
Date(("ORDDAT_0"), 'YYYY-MM-DD' ) as SO_OrderDate,
STOFCY_0 as Site,
ITMDES1_0 as ItemDesc,
SOQSTA_0 as Line_Status,
DEMSTA_0 as Status_In_Progress,
ORDSTA_0 as Order_Status,
NETPRI_0 as SO_NetPrice,
Num(SO_NETPRI_LOCAL) as SO_NETPRI_LOCAL,
Num(SHIQTY) as SHI_Qty,
BPCNAM_0 as Customer_Name,
FMI_0 as Product_Source,
TCLCOD_0 as Product_Category,
SHIDAT_0_SYNC as RateDate,
FMINUM_0 as B2B_Num,
Date(Today(), 'MM-DD-YYYY') as Date_Today,
SHTQTY_0 as Shortage_Qty,
ALLQTY_0 as Allocation_Qty,
;
SQL select
Q.SOHNUM_0,
Q.ITMREF_0,
Q.SOPLIN_0,
Q.QTY_0,
Q.SHIDAT_0,
Q.STOFCY_0,
M.ITMDES1_0,
CASE
when Q.SOQSTA_0 = '1' then 'Pending'
when Q.SOQSTA_0 = '2' then 'Late'
when Q.SOQSTA_0 = '3' then 'Closed'
END as SOQSTA_0,
CASE
when Q.DEMSTA_0 = '1' then 'Firm'
when Q.DEMSTA_0 = '2' then 'Planned'
when Q.DEMSTA_0 = '3' then 'Suggested'
when Q.DEMSTA_0 = '4' then 'Closed'
END as DEMSTA_0,
CASE
WHEN SO.ORDSTA_0 = '1' then 'Open'
WHEN SO.ORDSTA_0 = '2' then 'Closed'
END as ORDSTA_0,
SO.ORDDAT_0,
SD.SHIDAT_0 as SDSHIDAT,
P.NETPRI_0,
CASE
when P.SALFCY_0 = 'PISRO' then P.NETPRI_0*SO.CHGRAT_0 
when P.SALFCY_0 = 'PILTD' then P.NETPRI_0*SO.CHGRAT_0
END as SO_NETPRI_LOCAL,
SD.QTY_0 as SHIQTY,
SO.BPCNAM_0,
Q.FMI_0,
CASE
WHEN Q.FMI_0 like '1' then 'Normal'
WHEN Q.FMI_0 like '2' then 'PO - Direct to customer'
WHEN Q.FMI_0 like '3' then 'PO - Receive and ship'
WHEN Q.FMI_0 like '4' then 'Transfer'
WHEN Q.FMI_0 like '5' then 'Work order'
END as FMI_0,
M.TCLCOD_0,
FORMAT(Q.SHIDAT_0,'yyyy-MM') as SHIDAT_0_SYNC,
Q.FMINUM_0,
Q.SHTQTY_0,
Q.ALLQTY_0

FROM x3.PICLIVE.SORDERQ as Q
inner join x3.PICLIVE.ITMMASTER as M on M.ITMREF_0 = Q.ITMREF_0
inner join x3.PICLIVE.SORDER as SO on Q.SOHNUM_0 = SO.SOHNUM_0
left join x3.PICLIVE.SDELIVERYD as SD on Q.SOHNUM_0 = SD.SOHNUM_0 and Q.ITMREF_0 = SD.ITMREF_0 and SD.SOPLIN_0 = Q.SOPLIN_0
inner join x3.PICLIVE.SORDERP as P on Q.SOHNUM_0 = P.SOHNUM_0 and Q.SOPLIN_0 = P.SOPLIN_0 and Q.SOQSEQ_0 = P.SOPSEQ_0
;

TABCHANGE:
LOAD
"CHGRAT_0" as BaseChgRat,
CHGSTRDAT_0 as RateDate,
If( IsNull( CHGRAT_0 ), Peek( 'Field' ), CHGRAT_0 ) as Field,
;
SQL SELECT CURDEN_0,
CUR_0,
CHGRAT_0,
FORMAT(CHGSTRDAT_0,'yyyy-MM') as CHGSTRDAT_0
FROM x3.PICLIVE.TABCHANGE
where CURDEN_0 like 'GBP' and //destination currency,
CUR_0 like 'CZK' and
CHGTYP_0 = 1; // Rate Type - Daily rate

Thanks for your help.

[hic]

It's difficult to say without having the data...

But I have two suggestions:
1. Add "Order By CHGSTRDAT_0 Asc" to the SELECT statement, so that you know that the dates come in the right order.
2. Add "IsNull(CHGRAT_0) as DebugField," to the Load statement, to test that you really have NULLs.
(The field perhaps contains empty strings? If so, "IsNull(CHGRAT_0)" will return FALSE.)

[jarokall]

Thank you for your response.

Tried order and added Debug Field. It seems, there is emty string? 

I added a condition for empty string but still does not work.

If( IsNull( CHGRAT_0 ) or CHGRAT_0 ='', Peek( 'Field' ), CHGRAT_0 ) as Field

[hic]

You have a data problem. Since the DebugField doesn't have any values at all (not even 'FALSE'), it means that the table "x3.PICLIVE.TABCHANGE" doesn't have these dates (or the dates are removed by the WHERE clause).

If you need to populate these dates with values, you may need to join the two tables before using peek. Take a look at 

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/14706...

[vinieme12 · Accepted solution]

This looks like the table "SORDERQ" has data for dates from   2021-07 to 2024-06

but the table   x3.PICLIVE.TABCHANGE   only has data for dates from  2021-07 to 2022-06

You need to get all the dates in one table first before using peek() , otherwise there is nothing to peek() because CHGRAT_0  is never null as the dates from   2022-07  to 2024-06 don't exist in the table 

Add below script after loading "SORDERQ"

tempRates:
Load Distinct RateDate Resident SORDERQ;

Left Join(tempRates)
Load  CHGSTRDAT_0 as RateDate
,CHGRAT_0 as BaseChgRat
SQL SELECT
CHGRAT_0,
FORMAT(CHGSTRDAT_0,'yyyy-MM') as CHGSTRDAT_0
FROM x3.PICLIVE.TABCHANGE
where CURDEN_0 like 'GBP' and //destination currency,
CUR_0 like 'CZK' and
CHGTYP_0 = 1; // Rate Type - Daily rate

TABCHANGE:
Load 
RateDate 
,BaseChgRat
,if(len(BaseChgRat),BaseChgRat,peek('newBaseChgRat')) as  newBaseChgRat
,Resident tempRates
Order by RateDate ASC;

Drop table tempRates;