topic Re: Pick Match ApplyMap combined in QlikView

Pick Match ApplyMap combined

Wed, 25 Mar 2015 11:39:22 GMT

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

Re: Pick Match ApplyMap combined

MK_QSL — Wed, 25 Mar 2015 12:03:47 GMT

Can you provide sample data pls?

Re: Pick Match ApplyMap combined

Wed, 25 Mar 2015 12:14:42 GMT

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

Re: Pick Match ApplyMap combined

senpradip007 — Wed, 25 Mar 2015 12:27:31 GMT

PFA. Hope it will help.

Re: Pick Match ApplyMap combined

sunilkumarqv — Wed, 25 Mar 2015 12:27:45 GMT

If u want exactly same wat u want

then

simply you can below in script

'New'&' '&[Original Name] as New Name

FieldNameMap:

Mapping Load OldFieldName, NewFieldName From FieldNames ;

Rename Fields using FieldNameMap;

Re: Pick Match ApplyMap combined

Wed, 25 Mar 2015 17:16:56 GMT

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,

Re: Pick Match ApplyMap combined

chrismarlow — Wed, 25 Mar 2015 17:39:14 GMT

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.

Re: Pick Match ApplyMap combined

Thu, 26 Mar 2015 12:01:17 GMT

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")),

Re: Pick Match ApplyMap combined

chrismarlow — Mon, 06 Apr 2015 08:03:37 GMT

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