Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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/...
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:
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
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,
];
@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;
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
@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;
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
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: