topic Re: ApplyMap after crosstable in QlikView

ApplyMap after crosstable

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: ApplyMap after crosstable

stabben23 — Thu, 22 Dec 2016 11:33:58 GMT

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;

Re: ApplyMap after crosstable

swuehl — Thu, 22 Dec 2016 11:34:16 GMT

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.

Re: ApplyMap after crosstable

jessica_webb — Thu, 22 Dec 2016 11:37:18 GMT

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

Re: ApplyMap after crosstable

neha_shirsath — Thu, 22 Dec 2016 11:44:57 GMT

Hi Jessica,

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

Thanks & Regards,

Neha

Re: ApplyMap after crosstable

stabben23 — Thu, 22 Dec 2016 11:50:21 GMT

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;

Re: ApplyMap after crosstable

jessica_webb — Thu, 22 Dec 2016 11:50:34 GMT

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,

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'.

Re: ApplyMap after crosstable

sunny_talwar — Thu, 22 Dec 2016 11:54:13 GMT

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,

FROM xxxxx

JOIN (Data)

LOAD *,

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

Resident tempData;

DROP Table tempData;

Re: ApplyMap after crosstable

stabben23 — Thu, 22 Dec 2016 11:59:38 GMT

That is a better solution than mine.

Re: ApplyMap after crosstable

sunny_talwar — Thu, 22 Dec 2016 12:01:31 GMT

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

Re: ApplyMap after crosstable

jessica_webb — Thu, 22 Dec 2016 12:01:38 GMT

Perfect! Thanks Staffan!

Knew it couldn't be as tricky as I was finding it!

Really appreciate your help.

Re: ApplyMap after crosstable

jessica_webb — Thu, 22 Dec 2016 12:03:33 GMT

Agreed actually - sorry Staffan

Thanks Sunny, my script is looking even neater!

Huge thanks to you both.

Jess

Re: ApplyMap after crosstable

stabben23 — Thu, 22 Dec 2016 12:27:18 GMT

Resident load on Fact table should be avoid if not necessary.

Re: ApplyMap after crosstable

sunny_talwar — Thu, 22 Dec 2016 12:28:23 GMT

Agreed