topic Re: working with resident tables in QlikView

working with resident tables

Anonymous — Tue, 21 Apr 2015 21:35:11 GMT

Hi,

I would like to know which is the best way to work with qlick resident tables.

I have to create a qlick table from another qlick table, I can create the second table with the same name of the origin table or with diferent name and drop the origin table. Which is the best practice?. When I create both with the same name in the table viewer I have a "strange" model ($Sync_table ,table_name, and table_name-1)

I only want a table as a result.

Ventas2:

LOAD fecha,

periodo,

Applymap('Mapeo_semana', dia_semana) as dia_semana_desc,

ciudad,

pais,

cliente,

producto,

categoria,

venta,

margen,

tipo_servicio,

canal

RESIDENT Ventas;

DROP table Ventas;

Thanks in advance,

Re: working with resident tables

ramoncova06 — Tue, 21 Apr 2015 21:38:49 GMT

can you try a preceding load instead Simplify with Preceding Load | Qlikview Cookbook

the synthetic key is because of your joins and not because of the tables names

Re: working with resident tables

jaimeaguilar — Tue, 21 Apr 2015 21:44:33 GMT

Hi,

just like you mention, if you want just one table as a result, then the steps you'll need to follow are:

Load original table
residen load of the second table from the original table (make sure both tables are not exactly the same, otherwise they will auto-concatenate)
drop original table if it is not useful anymore.

Remember that the advantage of Resident load is that you take advantage of a table that is already in memory. Also, you have the alternative of storing a QVD file and then re-loading it,

regards

Re: working with resident tables

Anonymous — Tue, 21 Apr 2015 21:54:21 GMT

Looks like the whole purpose of this excerise is to replace dia_semana field with dia_semana_desc. I'd recommend to do it this way:

LEFT JOIN (Ventas) LOAD
dia_semana,
Applymap('Mapeo_semana', dia_semana) as dia_semana_desc,
RESIDENT Ventas;

DROP FIELD dia_semana;

Re: working with resident tables

Anonymous — Tue, 21 Apr 2015 21:54:27 GMT

The solution of Ramon is the best one with the preceding load

Ventas2:

load *,

Applymap('Mapeo_semana', dia_semana) as dia_semana_desc;

load

LOAD fecha,

periodo,

ciudad,

pais,

cliente,

producto,

categoria,

venta,

margen,

tipo_servicio,

canal

RESIDENT Ventas;

instead resident do what you did with our Ventas table

one word:

if you want to load from any datasource, you should define whether you want to concatenate to an existing

table or noconcatetane if you want an own table. this is not mandatory, but it clarifies if you read your scriptiew

(qlikview ill concatenate if the field names are identical, but you will not see it from the script)

Ventas2:

concatenate (Ventas) load

...

or Ventas2:

noconcatenate load

your syntetic key table results in same fieldnames in both table (table_Name and table_name1)

Re: working with resident tables

ziadm — Tue, 21 Apr 2015 21:55:12 GMT

Hi Juan

The best practice for using a resident table is load a raw of data into a one Table DATA1. This is the source of data for making other tables such as aggregated table over dimensions (ie sum of sales group by product .. etc)..

also making your Master Calendar table, having all range of dates.

The raw table may also optimized by using applymap and many transformation functions in order to reach optimal table.

Having the DATA1 resident to load is faster than loading the data again from a QVD files or data base.

The practice is to finally drop the DATA1 table after having the extraction and transformation in an optimized data model

Hope this is useful

Regards

Ziad