Replace text in field using applymap and composite key?

[wardzynski]

Is it possible to just replace some content (text) of a field using applymap and a composite key?

FacilityWardMap2:

Mapping Load:

LOAD Facility&'-'&Ward as MapID,

     [Ward Type]

FROM

(biff, embedded labels, table is Sheet1$);

tempMainTable2:

LOAD

    Facility&Ward as MapIDMain,

    *

Resident tempMainTable;

Drop Table tempMainTable;

MainTable:

LOAD

    ApplyMap ('FacilityWardMap2', MapIDMain, [Ward Type]) as [Ward Type_New],

    *

Resident tempMainTable2;

Drop Table tempMainTable2;

[shraddha_g]

yes it is..

but key should be same in both mapping table and main table.

FacilityWardMap2:

Mapping Load:

LOAD Facility&'-'&Ward as MapID,

     [Ward Type]

FROM

(biff, embedded labels, table is Sheet1$);

tempMainTable2:

LOAD

     Facility&'-'&Ward as MapIDMain,

    *

Resident tempMainTable;

Drop Table tempMainTable;

MainTable:

LOAD

    ApplyMap ('FacilityWardMap2', MapIDMain, [Ward Type]) as [Ward Type_New],

    *

Resident tempMainTable2;

Drop Table tempMainTable2;

[Anil_Babu_Samineni]

MapID and MapIDMain syntax and field structure of values should be same format..

[wardzynski]

I noticed that, but I still do t er the results I want. The old value is

still there

On Mon, 25 Sep 2017 at 1:51 pm, Shraddha Gajare <qcwebmaster@qlikview.com>

[its_anandrjs]

Some changes in your script after the cleansing of the code hove a look with key names should be unique (Facility&Ward) and why you use Ward Type in Applymap it should be not there

ApplyMap ('FacilityWardMap2', MapIDMain, '[Ward Type]')

FacilityWardMap2:

Mapping Load

LOAD Facility&Ward as MapID,

     [Ward Type]

FROM

(biff, embedded labels, table is Sheet1$);

tempMainTable2:

LOAD

    Facility&Ward as MapIDMain,

    *

Resident tempMainTable;

Drop Table tempMainTable;

MainTable:

LOAD

    ApplyMap ('FacilityWardMap2', MapIDMain, 'NONE') as [Ward Type_New],

    *

Resident tempMainTable2;

Drop Table tempMainTable2;

[shraddha_g]

can you share sample app with sample data?

[wardzynski]

I tried all of that, it just does not want to replace the [Ward Type] value, it is still there.

What I want to do is replace [Ward Type] where the value is "unmapped" etc... with a mapped value from an Mapping Load Table.

[its_anandrjs · Accepted solution]

I believe you cant because when you map this key field Facility&Ward is matching with your Main table with key MapIDMain and if they matches [Ward Type] is provided from table FacilityWardMap2 on your Main table and if they not you have to use 'NONE'

FacilityWardMap2:

Mapping Load

LOAD Facility&Ward as MapID,

     [Ward Type]

FROM

(biff, embedded labels, table is Sheet1$);

tempMainTable2:

LOAD

    Facility&Ward as MapIDMain,

    *

Resident tempMainTable;

Drop Table tempMainTable;

MainTable:

LOAD

    ApplyMap ('FacilityWardMap2', MapIDMain, 'NONE') as [Ward Type_New],

    *

Resident tempMainTable2;

Drop Table tempMainTable2;

[wardzynski]

HI Anand,

Thank you for your reply!

Yes, I tried this, and apparently there are some [Ward Types]:s that are missing inside my Mapping Table, as you can see in the screenshot, but the unmapped and unknow values are replaced with the correct ones.

So, when I have all the mapping values correct, I should just be able to  replace [Ward Type New] with [Ward Type]  like this:

Let me know if there is a better way to do this Anand?

FacilityWardMap2:

Mapping Load

Facility&'-'&Ward as MapID,

     [Ward Type]

FROM

[File.xls]

(biff, embedded labels, table is Sheet1$);

tempMainTable2:

LOAD

Facility&'-'&Ward as MapIDMain,

*

Resident tempMainTable;

Drop Table tempMainTable;

NoConcatenate

tempMainTable3:

LOAD

MapIDMain,

DataDate,

...// all fields except [Ward Type]

...// all fields except [Ward Type]

ApplyMap ('FacilityWardMap2', MapIDMain,'NONE') as [Ward Type]

Resident tempMainTable2;

Drop Table tempMainTable2;