topic Re: Binary Load x Mapping Table in QlikView

Binary Load x Mapping Table

Clever_Anjos — Tue, 13 Sep 2016 19:01:08 GMT

I would like to have a mapping table coming from a datamart qvw loaded through binary load.

Unfortunately, a table created like below is not loaded when the binary is executed

Prefixos:
mapping load * ;
sql SELECT DISTINCT
  PREFIXO,
  isnull(b.OPERADORA_TRATADA,PRESTADORA) as PRESTADORA
FROM TB_PREFIXOS a
left join TB_OPERADORAS b on a.PRESTADORA = b.OPERADORA;

I´m using a workaround like this that is not so optimized as I would like

Datamart.qvw

Prefixos:
sql SELECT DISTINCT
  PREFIXO,
  isnull(b.OPERADORA_TRATADA,PRESTADORA) as PRESTADORA
FROM TB_PREFIXOS a
left join TB_OPERADORAS b on a.PRESTADORA = b.OPERADORA;

Dashboard.qvw

Binary [Datamart.qvw];
MapaPrefixos:
Mapping LOAD * Resident Prefixos;
Drop Table Prefixos;

Any ideas how could I improve it?

Re: Binary Load x Mapping Table

sunny_talwar — Tue, 13 Sep 2016 19:15:30 GMT

I think the problem here is that the mapping table won't exist after the Datamart.qvw runs through and if it doesn't exits, you would not be able to do a applymap on it. I think what you have is one of the option, the other being bringing the mapping load table directly in your new qvw after saving it into a qvd.

Prefixos:

mapping

LOAD DISTINCT

PREFIXO,

isnull(b.OPERADORA_TRATADA,PRESTADORA) as PRESTADORA

FROM TB_PREFIXOS.qvd (qvd);

Re: Binary Load x Mapping Table

Peter_Cammaert — Tue, 13 Sep 2016 20:15:55 GMT

Sunny is right. The lifetime of a Mapping table extends to the end of the current script run. Not a second longer.. Mapping tables do not survive the current cycle as they aren't really meant to be more than temporary but extremeley fast translation tables. Ad hoc solutions ("hacks") so to say. A mapping table is also - and by design - never a part of a data mart.

If you need a mapping table in different places, recreate it in those places. It makes no use to load it in one run and use it only in the next run. Although you may have sensible use cases.

Re: Binary Load x Mapping Table

MarcoWedel — Tue, 13 Sep 2016 21:08:28 GMT

Hi Clever,

maybe using some loop to identify tables with names like 'Mapa*' and mapping loading them resident, droping the source table afterwards.

You just would need to purge the mapping prefix in your Datamart.qvw and could include such a code snippet as some sort of standard transformation in your target qvws.

hope this helps

regards

Marco

Re: Binary Load x Mapping Table

Clever_Anjos — Tue, 13 Sep 2016 21:52:39 GMT

Thank you all.

As I was thinking a mapping table does not survive the end of script;

I was hoping there´s a "hack", but it seems there´s not

Going to test what is faster:

Resident load of a table from datamart
Loading a qvd (unfortunately a mapping load seems to discard a optimized load too )