Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Pick Match ApplyMap combined

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 NameNew Name
JohnNew John
PaulNew Paul
JakeNew Jake
TomNew Tom
TimNew Tim
PeterNew 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

8 Replies
MVP
MVP

Re: Pick Match ApplyMap combined

Can you provide sample data pls?

Not applicable

Re: Pick Match ApplyMap combined

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 NameNew Name
JohnNew John
PaulNew Paul
JakeNew Jake
TomNew Tom
TimNew Tim
PeterNew Peter
senpradip007
Valued Contributor III

Re: Pick Match ApplyMap combined

PFA. Hope it will help.

sunilkumarqv
Valued Contributor II

Re: Pick Match ApplyMap combined

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;


Not applicable

Re: Pick Match ApplyMap combined

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:

OriginalNameNewName
John OldJohn 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,

chrismarlow
Contributor III

Re: Pick Match ApplyMap combined

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.

Not applicable

Re: Pick Match ApplyMap combined

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_keyOriginal_NameNew_Name
1JohnNew John
2PaulNew Paul
3JakeNew Jake
4TimNew Tim
5PeterNew Peter

Is this the way to write the code?

([Original_ ApplyMap('Map_Original_name', "Original_Name_key"),ApplyMap('Map_New_Name', ""Original_Name_key")),

chrismarlow
Contributor III

Re: Pick Match ApplyMap combined

Do you need to? Would you not map Original_Name_Key straight to New_Name?

Community Browser