Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Contributor II
Contributor II

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
Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor II
Contributor II

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

 

Highlighted
MVP
MVP

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

Highlighted
Contributor II
Contributor II

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

 

Highlighted
MVP
MVP

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

Highlighted

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 

 

Highlighted
Contributor II
Contributor II

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