Load value from previously loaded column from current row

MaSo1996 — Fri, 25 Jul 2025 11:37:40 GMT

Hello Fellow Qlik-Enjoyers!

Since yesterday I'm trying to create table with 7 columns, that are calculated based on previously loaded in the same row columns. Below script, I'm currenty using and doesn't work, as I ecpect:

NoConcatenate
TempTable:
load
ParentItem,
if(IsNull(Product2),peek(ParentItem),Product2) as Product2,
if(IsNull(Product3),peek(Product2),Product3) as Product3,
if(IsNull(Product4),peek(Product3),Product4) as Product4,
if(IsNull(Product5),peek(Product4),Product5) as Product5,
if(IsNull(Product6),peek(Product5),Product6) as Product6,
if(IsNull(Product7),peek(Product6),Product7) as Component
Resident BoM_2024;

The idea for me is following - if present value I try to get is empty, I want it to be value from prvious column - this is how the table BoM_2024 looks like:

ParentItem	Product2	Product3	Product4	Product5	Product6	Product7
VPC430OEL_0000	VPC430OEL	KAPTUR180	-	-	-	-
VPC430OEL_0000	VPC430OEL	KGGL250_0000	-	-	-	-
VPC430OEL_0000	VPC430OEL	L3063	-	-	-	-
VPC430OEL_0000	VPC430OEL	L3073	-	-	-	-
VPC430OEL_0000	VPC430OEL	L3083	-	-	-	-
VPC430OEL_0000	VPC430OEL	LTANGENTIAL	-	-	-	-
VPC430OEL_0000	VPC430OEL	SO.201.10.005	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC.201.13.7700	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC.201.13.61190	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC.201.201OEL	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC.201.3550	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC.201.4550-1	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC.201.4550GRAU	-	-	-	-
VPC430OEL_0000	VPC430OEL	VPC430HZOEL	VPC.201.611	-	-	-
VPC430OEL_0000	VPC430OEL	VPC430HZOEL	VPC.201.630	-	-	-
VPC430OEL_0000	VPC430OEL	81300701	-	-	-	-
VPC430OEL_0000	VPC430OEL	81400605	-	-	-	-

And this is, what I would like to achievie:

ParentItem	Product2	Product3	Product4	Product5	Product6	Product7
VPC430OEL_0000	VPC430OEL	KAPTUR180	KAPTUR180	KAPTUR180	KAPTUR180	KAPTUR180
VPC430OEL_0000	VPC430OEL	KGGL250_0000	KGGL250_0000	KGGL250_0000	KGGL250_0000	KGGL250_0000
VPC430OEL_0000	VPC430OEL	L3063	L3063	L3063	L3063	L3063
VPC430OEL_0000	VPC430OEL	L3073	L3073	L3073	L3073	L3073
VPC430OEL_0000	VPC430OEL	L3083	L3083	L3083	L3083	L3083
VPC430OEL_0000	VPC430OEL	LTANGENTIAL	LTANGENTIAL	LTANGENTIAL	LTANGENTIAL	LTANGENTIAL
VPC430OEL_0000	VPC430OEL	SO.201.10.005	SO.201.10.005	SO.201.10.005	SO.201.10.005	SO.201.10.005
VPC430OEL_0000	VPC430OEL	VPC.201.13.7700	VPC.201.13.7700	VPC.201.13.7700	VPC.201.13.7700	VPC.201.13.7700
VPC430OEL_0000	VPC430OEL	VPC.201.13.61190	VPC.201.13.61190	VPC.201.13.61190	VPC.201.13.61190	VPC.201.13.61190
VPC430OEL_0000	VPC430OEL	VPC.201.201OEL	VPC.201.201OEL	VPC.201.201OEL	VPC.201.201OEL	VPC.201.201OEL
VPC430OEL_0000	VPC430OEL	VPC.201.3550	VPC.201.3550	VPC.201.3550	VPC.201.3550	VPC.201.3550
VPC430OEL_0000	VPC430OEL	VPC.201.4550-1	VPC.201.4550-1	VPC.201.4550-1	VPC.201.4550-1	VPC.201.4550-1
VPC430OEL_0000	VPC430OEL	VPC.201.4550GRAU	VPC.201.4550GRAU	VPC.201.4550GRAU	VPC.201.4550GRAU	VPC.201.4550GRAU
VPC430OEL_0000	VPC430OEL	VPC430HZOEL	VPC.201.611	VPC.201.611	VPC.201.611	VPC.201.611
VPC430OEL_0000	VPC430OEL	VPC430HZOEL	VPC.201.630	VPC.201.630	VPC.201.630	VPC.201.630
VPC430OEL_0000	VPC430OEL	81300701	81300701	81300701	81300701	81300701
VPC430OEL_0000	VPC430OEL	81400605	81400605	81400605	81400605	81400605

You have any ideas, how to achieve that?

Re: Load value from previously loaded column from current row

lennart_mo — Fri, 25 Jul 2025 13:32:48 GMT

Hi @MaSo1996,

As far as I know, Qlik isn't able to reference calculated values from the same row, peek would in this case return the value from the previous row.

To achieve your desired output I'd probably use the coalesce function (coalesce - script and chart function | Qlik Sense on Windows Help), which returns the first string <> NULL from multiple expressions.

So the loadscript for your output might look something like this:

NoConcatenate
TempTable:
load
ParentItem,
coalesce(Product2, ParentItem) as Product2,
coalesce(Product3, Product2, ParentItem) as Product3,
coalesce(Product4, Product3, Product2, ParentItem) as Product4,
coalesce(Product5, Product4, Product3, Product2, ParentItem) as Product5,
coalesce(Product6, Product5, Product4, Product3, Product2, ParentItem) as Product6,
coalesce(Product7, Product6, Product5, Product4, Product3, Product2, ParentItem) as Component
Resident BoM_2024;

Re: Load value from previously loaded column from current row

MaSo1996 — Fri, 25 Jul 2025 13:44:53 GMT

Thank You @lennart_mo , this is exactly what I needed!

topic Re: Load value from previously loaded column from current row in App Development

Load value from previously loaded column from current row

Re: Load value from previously loaded column from current row

Re: Load value from previously loaded column from current row