topic Re: Problems ussing Mapping and Applymap in QlikView

Problems ussing Mapping and Applymap

Thu, 22 Aug 2013 16:13:11 GMT

Hi to all

Im trying to use the Mapping and ApplyMap concepts. But im having problems.

Let me explain the goal that i wanna to achieve:

The main problem its that i have a huge database in terms of quantity of records. around 15 millons per week.

The problem its that i have to find the way to reduce the cost of memory used to load all the records.

So, one options its the mapping way, as i read in others post. If someone have other solutions, please, feel free to tell it.

So. My idea its to bring out those fields that are used to describe something, like type of Call, or Vendor, and so on.

I have not a paralel database with cod and description so, the first thing i do its to create one with a group by command:

ResumenCALLCENTER:

load

CALLCENTER,

count(DISTINCT CALLCENTER) as Cuenta

resident Llamados_Tot

group by CALLCENTER;

Then, i try to apply the mapping concept:

MATCHCALLCENTER:

Mapping LOAD

AutoNumber(CALLCENTER) as Codigo_CALLCENTER,

CALLCENTER

Resident ResumenCALLCENTER;

I use the autonumber functions because, like i said, i have not a reference database.

After that, i try to apply the applymap concept in load sentence:

LLAMADOS_EXT:

LOAD IDCALL,

IDCALL_Cont,

ORIGEN,

ApplyMap('MATCHCALLCENTER', CALLCENTER, 'SinDatos') AS CALLCENTER,

vendedor

FROM

E:\Datos\Llamados_Ext_Bim_012013.qvd (qvd);

What im doing in the wron way? If I replace "CALLCENTER" by Codigo_CALLCENTER in the applymap sentence, it doesnt work.

And in this way, it works but returns allways "SinDatos"

Someone can help me?

Thanks. Regards.

Re: Problems ussing Mapping and Applymap

abeyphilip — Thu, 22 Aug 2013 16:20:22 GMT

How ApplyMap works in the below case is :

ApplyMap('MATCHCALLCENTER', CALLCENTER, 'SinDatos') AS CALLCENTER,

it will try to match each

CALLCENTER value in E:\Datos\Llamados_Ext_Bim_012013.qvd

with the

AutoNumber(CALLCENTER) as Codigo_CALLCENTER in MATCHCALLCENTER.

If it find a match then it returns
matching CALLCENTER value from MATCHCALLCENTER or else SinDatos.

Regards,

Abey

Re: Problems ussing Mapping and Applymap

rajeshvaswani77 — Thu, 22 Aug 2013 16:24:18 GMT

Hi,

You could pass autonumber(CALLCENTER) to teh applymap.

tahnks,

Rajesh Vaswani

Re: Problems ussing Mapping and Applymap

Thu, 22 Aug 2013 16:26:25 GMT

Thanks for the answer Abey, but, i have not yet clear what im doing wrong, because the only value that im receiveing its the "SinDatos" value for those cases that are not matched...

Could you give me some idea?

Re: Problems ussing Mapping and Applymap

Thu, 22 Aug 2013 16:28:27 GMT

Rajesh, could you give me a sintaxis example ?

Re: Problems ussing Mapping and Applymap

rajeshvaswani77 — Thu, 22 Aug 2013 16:32:04 GMT

Please use Applymap('MATCHCALLCENTER',autonumber(CALLCENTER),'Sindatos') as CALLCENTER

Re: Problems ussing Mapping and Applymap

Oleg_Troyansky — Thu, 22 Aug 2013 16:46:24 GMT

German,

I don't think you can use Autonumber this way... The function Autonumber guarantees to translate an identical string to an identical number within the same QVW document.

When you are loading data from a QVD file, generated in other application, the Autonumber function used there won't return the same results as the autonumber function here.

Also, when you are loading a HUGE QVD file, you need to keep the load statement clear of any additional calculations, to preserve the "optimized load". Adding autonumber there will make the load non-optimizied, which is much slower.

If you describe exactly what are you trying to accomplish here, I can try to help with the correct syntax.

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

Re: Problems ussing Mapping and Applymap

Thu, 22 Aug 2013 17:48:01 GMT

Oleg

thanks for your time

In the first part of my question i described what im looking for:

"The main problem its that i have a huge database in terms of quantity of records. around 15 millons per week.

The problem its that i have to find the way to reduce the cost of memory used to load all the records."

Can you help me ?

Thanks

Re: Problems ussing Mapping and Applymap

rajeshvaswani77 — Thu, 22 Aug 2013 17:53:59 GMT

Hi,

Please look at the white papers that are attached to this post.

thanks,

Rajesh Vaswani

Re: Problems ussing Mapping and Applymap

rajeshvaswani77 — Thu, 22 Aug 2013 17:54:21 GMT

http://community.qlik.com/message/313963#313963

Re: Problems ussing Mapping and Applymap

Oleg_Troyansky — Thu, 22 Aug 2013 18:21:07 GMT

German,

this is a very open question, it would probably require a consulting engagement to review your data model and to find where you can save memory. I teach a whole 2-hour session just on that topic at the Masters Summit event (check it out, by the way).

Looking at your code, I don' believe that applying Mapping to the CALLCENTER field could contribute to memory savings. You should find and download tools like QlikVIew Optimizer, or Document Analyzer from Rob Wunderlich (www.robwunderlich.com) and try to analyze your memory footprint and how it can be reduced.

If you have a specific question on a specific memory-saving technique, I'd be happy to answer it here.

cheers,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com/blog

Re: Problems ussing Mapping and Applymap

Thu, 22 Aug 2013 19:15:52 GMT

Thanks, but no one of the articules speek about treatment of big cuantyties of datas endeed...

Regards