topic Re: Replace text in field using applymap and composite key? in QlikView

Replace text in field using applymap and composite key?

wardzynski — Mon, 25 Sep 2017 03:38:09 GMT

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;

Re: Replace text in field using applymap and composite key?

shraddha_g — Mon, 25 Sep 2017 03:51:20 GMT

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;

Re: Replace text in field using applymap and composite key?

Anil_Babu_Samineni — Mon, 25 Sep 2017 03:59:13 GMT

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

Re: Replace text in field using applymap and composite key?

wardzynski — Mon, 25 Sep 2017 04:00:20 GMT

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>

Re: Replace text in field using applymap and composite key?

its_anandrjs — Mon, 25 Sep 2017 04:00:45 GMT

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;

Re: Replace text in field using applymap and composite key?

shraddha_g — Mon, 25 Sep 2017 04:00:59 GMT

can you share sample app with sample data?

Re: Replace text in field using applymap and composite key?

wardzynski — Mon, 25 Sep 2017 04:19:25 GMT

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.

Re: Replace text in field using applymap and composite key?

its_anandrjs — Mon, 25 Sep 2017 04:33:39 GMT

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;

Re: Replace text in field using applymap and composite key?

wardzynski — Mon, 25 Sep 2017 08:25:43 GMT

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]

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

Resident tempMainTable2;

Drop Table tempMainTable2;