working with resident tables

[Anonymous]

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,

[ramoncova06 · Accepted solution]

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

[jaimeaguilar]

Hi,

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

1. Load original table
2. residen load of the second table from the original table (make sure both tables are not exactly the same, otherwise they will auto-concatenate)
3. 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

[Anonymous]

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;

[Anonymous]

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)

[ziadm]

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