Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Please kindly advise on the best approach.
I have a mapping table in an Excel spreadsheet that will be loaded into Qlikview as part of my data model.
If a condition is meant, I want the name to change from Original Name to New Name
Original Name | New Name |
John | New John |
Paul | New Paul |
Jake | New Jake |
Tom | New Tom |
Tim | New Tim |
Peter | New Peter |
Please how can I replace Original Name with New Name in a script. For examle, if a condition is meant I want the name to change from i.e. John to New John.
I was explore Pick(Match(ApplyMap.....)
Also, can you kindly describe how the Pick(Match( function works?
Regards
Can you provide sample data pls?
The sample data is in an Excel spreadsheet
Column A (Original Name)
Cloumn B (New Name)
I want to replace Column A with Column B
Original Name | New Name |
John | New John |
Paul | New Paul |
Jake | New Jake |
Tom | New Tom |
Tim | New Tim |
Peter | New Peter |
PFA. Hope it will help.
If u want exactly same wat u want
then
simply you can below in script
'New'&' '&[Original Name] as New Name
or
FieldNameMap:
Mapping Load OldFieldName, NewFieldName From FieldNames ;
Rename Fields using FieldNameMap;
Hi Pradip and all,
Unfortunately, it does not seem like what I want.
This is the scenario:
In my data model I have a field called SalesPerson (SP). Let's called this SP_qvd
I have a SalesPerson table in the data warehouse - this holds static data. Let's call this SP_ETL.
When there is a change in sales person name, instead of updating SP_ETL I update an Excel sheet that feeds into my data model (SP_qvd). Let's call the Excel sheet SP_xls.
SP_xls has two columns: OriginalName and NewName. Column OriginalName is also present in SP_ETL.
John Old, a sales man has changed his name to John New. The first step is to update SP_xls below:
OriginalName | NewName |
John Old | John New |
Question:
Please how do I write the logic below:
IF SP_xls.OriginalName EXIST IN SP_ETL.OrignalName
CHANGE/UPDATE SP_qvd WITH SP_xls.NewMan
Regards,
I think Pradip's QVW does get you most of the way there.
Instead of Inline load for your mapping table pull from the SP_xls.
The apply map will return the Original_Name into NewNameField where not in the map & can be applied as you load from SP_ETL.
Hi Pradip/Chrstopher,
I have looked at Pradip qvw and it suits me.
However, this is a question. In the event SP_xls having three columns (including a unique key column)
Original_Name_key | Original_Name | New_Name |
1 | John | New John |
2 | Paul | New Paul |
3 | Jake | New Jake |
4 | Tim | New Tim |
5 | Peter | New Peter |
Is this the way to write the code?
([Original_ ApplyMap('Map_Original_name', "Original_Name_key"),ApplyMap('Map_New_Name', ""Original_Name_key")),
Do you need to? Would you not map Original_Name_Key straight to New_Name?