Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bisaack55
Contributor II
Contributor II

Load script - replace values if matches another row same table

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

ForecastDate.png

The end result should look like this;

ForecastDate02.png

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

 

Labels (3)
1 Solution

Accepted Solutions
bisaack55
Contributor II
Contributor II
Author

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:

View solution in original post

7 Replies
Vegar
MVP
MVP

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

bisaack55
Contributor II
Contributor II
Author

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,
];

ForecastDate03.png

 

Kushal_Chawda

@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;

bisaack55
Contributor II
Contributor II
Author

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

 

Kushal_Chawda

@bisaack55  try below

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 DateField
RESIDENT DATA
ORDER BY PartNumber, Category;

avinashelite

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 

 

bisaack55
Contributor II
Contributor II
Author

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: