ApplyMap after crosstable

[jessica_webb]

Hello,

I have a problem which I feel should be straightforward to solve. But I can't do it...

Basically, I have a table called Data.

I then load a temp table as a crosstable, called 'tempData'.

I then left join my tables:

LEFT JOIN (Data) LOAD * Resident tempData;

Drop TABLE tempData;

Now, I want to applymap to one of the fields that was in tempData, but is now in Data.

How do I do this?

Many thanks,
Jess

[stabben23]

Hi,

maybe you could try to do a mapping Load before you drop the tempData.

LEFT JOIN (Data) LOAD * Resident tempData;

Mapping LOAD

map:

field1 as Mapkey,

field2 as mapfield

resident tempData;

Drop TABLE tempData;

[swuehl]

Before you can use ApplyMap() function, you need to load a MAPPING table:

Mapping … and not the geographical kind

You can then use your mapping table using an ApplyMap() e.g. in the RESIDENT LOAD of your tempData table.

[jessica_webb]

Hi Stefan,

I should have explained that I have already loaded the mapping table.

How would I applymap in the resident load? That's the bit I'm stuck on!

Thanks,

Jess

[neha_shirsath]

Hi Jessica,

Can you put some sample data for brief idea of your issue.

Thanks & Regards,

Neha

[stabben23 · Accepted solution]

Hi Jessica,

you can load the Data table once agin With a resident LOAD.

Data2:

LOAD

*,

applymap('map',field) as fieldNew

Resident Data;

drop table Data;

[jessica_webb]

Hi Neha,

Of course!

Map_New:

Mapping LOAD * INLINE [

Given,New

A,1

B,2

];

Data:

LOAD

ID,

Type

FROM xxxxxxxx

tempData:

CrossTable(Given,Outcome,1)

LOAD

ID,

A,

B

FROM xxxxx

JOIN (Data) LOAD* Resident tempData;

Drop TABLE tempData;

At this point, I want to applymap with 'Map_New', so that in my  'Data' table, I am left with ID, Type, New, Outcome

and can drop 'Given'.

[sunny_talwar]

May be use ApplyMap when you are joining?

Map_New:

Mapping LOAD * INLINE [

Given,New

A,1

B,2

];

Data:

LOAD

ID,

Type

FROM xxxxxxxx

tempData:

CrossTable(Given,Outcome,1)

LOAD

ID,

A,

B

FROM xxxxx

JOIN (Data)

LOAD *,

          ApplyMap('Map_New', FieldName, Null()) as NewFieldName

Resident tempData;

DROP Table tempData;

[stabben23]

That is a better solution than mine.

[sunny_talwar]

I guess the only good thing here is that we have one less load