topic Generate missing fields with peek() not work in App Development

Generate missing fields with peek() not work

jarokall — Wed, 01 Jun 2022 06:04:55 GMT

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.

Re: Generate missing fields with peek() not work

hic — Wed, 01 Jun 2022 07:09:05 GMT

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.)

Re: Generate missing fields with peek() not work

jarokall — Wed, 01 Jun 2022 07:35:16 GMT

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

Re: Generate missing fields with peek() not work

hic — Wed, 01 Jun 2022 07:48:30 GMT

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/1470637

Re: Generate missing fields with peek() not work

vinieme12 — Wed, 01 Jun 2022 07:44:53 GMT

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;