topic Load script - replace values if matches another row same table in QlikView

Load script - replace values if matches another row same table

bisaack55 — Mon, 26 Jan 2026 18:19:17 GMT

Hi QV Community,

I have a table that not every row has a value, and I need to fill in the value if it matches a criteria.

Rows with Category "A" will always have a date value but rows with category "B" are always empty from the data source, I need to copy the date value from category "A" to the rows with category "B" - with the condition that unique "PartNumber" value matches only.

The screenshot below is a example of the data, the rows with category "B" the date value is always empty but if you notice the Part number matches for a few rows

The end result should look like this;

the rows with category "B" if the part number matches from row with category "A" the date value gets updated on those rows

I have tried copy from above QlikView function but in the QlikView front end app and table the sorting is not always on the above row, the only unique identifier is the "PartNumber" value https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/InterRecordFunctions/above.htm

Re: Load script - replace values if matches another row same table

Vegar — Mon, 05 Oct 2020 20:04:51 GMT

You can do this using different methods.

Method 1.

Final:
NoConcatenate
LOAD
Category,
PartNumber,
IF(Peek('Category')='B', Peek('Date'),Date) as Date
RESIDENT DATA
ORDER BY PartNumber, Category;

Method 2

Final:
Load PartNumber, Date
Resident Data
Where Category ='A';

LEFT JOIN (Final)
LOAD Category, PartNumber
Resident Data;

Method 3

Map_date:
Mapping Load
PartNumber,
Date
Resident Data
Where Category ='A';

Final:
LOAD Category, PartNumber, Applymap('Map_date', PartNumber, Date) as Date
Resident Data;

I hope one of these suggestions would fit your situation.

BR
Vegar

Re: Load script - replace values if matches another row same table

bisaack55 — Mon, 05 Oct 2020 22:04:16 GMT

Thank Vegar for quick your response, I tried the 1st method but i was getting the same date value on the row Category ="A" but not in the rows where the category = "B"

The other 2 options i could not use because it is a large table and there are more than 2 categories approx. 20 different type of categories for the same PartNumber in other word the row with Category "A" part number "12345" there is a date value, there could be Category B, C, D, E, F etc... all these rows do not have a DATE value i have to copy the DATE value from row with category "A" to all the same rows that match the same part number, this could be 20 rows or more of the same partNumber

Maybe the original [DateField] can keep the raw values from the database and there could be a calculated field [NewDateField] that will have the date value from category ="A" and PartNumber matches the same row regardless if that row is category B, C, D, E, F etc...

LOAD * INLINE [
F1, F2, F3
Category, PartNumber, DateField
A, 12345, 10/5/2020
B, 12345,
B, 12345,
B, 12345,
A, 67890, 10/6/2020
B, 67890,
B, 67890,
B, 67890,
B, 67890,
A, 98765, 10/7/2020
B, 98765,
B, 98765,
B, 98765,
B, 98765,
C, 98765,
D, 98765,
F, 98765,
];

Re: Load script - replace values if matches another row same table

Kushal_Chawda — Mon, 05 Oct 2020 22:12:02 GMT

@bisaack55 just bit modification of what @Vegar suggested

Final:
NoConcatenate
LOAD
Category,
PartNumber,
IF(len(trim(Date))=0,Peek('Date'),Date) as Date
RESIDENT DATA
ORDER BY PartNumber, Category;

Re: Load script - replace values if matches another row same table

bisaack55 — Mon, 05 Oct 2020 23:32:53 GMT

Hi Kush, I tried that but i get null values on the rows the category is not "A"

LOAD * INLINE [
Category, PartNumber, DateField
A, 12345, 10/5/2020
B, 12345,
B, 12345,
B, 12345,
A, 67890, 10/6/2020
B, 67890,
B, 67890,
B, 67890,
B, 67890,
A, 98765, 10/7/2020
B, 98765,
B, 98765,
B, 98765,
B, 98765,
C, 98765,
D, 98765,
F, 98765,
];

Final:
NoConcatenate
LOAD
Category,
PartNumber,
IF(len(trim(DateField))=0,Peek('DateField'),DateField) as NewDateField
RESIDENT DATA
ORDER BY PartNumber, Category;

The rows with Category = "A" i get a DateField values, but if i filter for Caterigy B, C, D, E, F etc... these rows are null to begin with and the i also can not ORDER BY PartNumber, Category; because this is a large table and on the front end charts get wrong data now after i added that to the load script

Re: Load script - replace values if matches another row same table

Kushal_Chawda — Tue, 06 Oct 2020 00:00:04 GMT

@bisaack55 try below

Final:
NoConcatenate
LOAD
Category,
PartNumber,
IF(len(trim(DateField))=0,Peek('DateField'),DateField) as DateField
RESIDENT DATA
ORDER BY PartNumber, Category;

Re: Load script - replace values if matches another row same table

avinashelite — Tue, 06 Oct 2020 00:25:40 GMT

If you can't order by then just add the Kush expression in the load statement and try

IF(len(trim(DateField))=0,Peek('DateField'),DateField) as DateField

this will work

Re: Load script - replace values if matches another row same table

bisaack55 — Wed, 07 Oct 2020 00:00:25 GMT

Hi Vegar, and everyone who replied.

After trying multiple options suggested by you guys i got it to work with a little different approach

////////////////////////////////////

Original_Data:

Load

Category,

PartNumber,

DateField

From [\\serverpath\data.qvd] (qvd):

///////////// mapping table///////

Date_Mappling:

Mapping LOAD

PartNumber,

DateField

Resident Original_Data

Where

Category = 'A'

Final_Data:

Load *,

IF(WildMatch(Category,'B','C','D','E','etc' ////include all other categories

// AND len([DateField])=0 //// i found out i dont need to check if the date is null since it is always 100% null from the data source

ApplyMap('Date_Mapping',[PartNumber],null()), [DateField]) as [DateField] ///PartNumber is the key field from the mapping table

Resident Original_Data;

Drop Table Original_Data: