topic Re: new table when using applymap with resident in QlikView

new table when using applymap with resident

curiousfellow — Fri, 29 Jul 2016 09:38:01 GMT

In some examples of how to use applymap tables are "resused".

In Don't join - use Applymap instead

the code is :

Orders :

Load *

Applymap .......

from Orders ;

When I do the same I get a second table with the same name added "-1" to it.

Only difference in my script is that I load from a resident table.

Is it not possible to add a field with applymap to a resident table ?

Re: new table when using applymap with resident

sunny_talwar — Fri, 29 Jul 2016 09:40:03 GMT

Not sure I understand the issue? In the attached application what issue have you been facing?

Re: new table when using applymap with resident

alexandros17 — Fri, 29 Jul 2016 09:41:52 GMT

Use the noconcatenate Function:

addres_New: noconcatenate

load *,

Applymap('map_city_to_addres',number,null()) as city

resident address

drop table address;

let me know

Re: new table when using applymap with resident

jonathandienst — Fri, 29 Jul 2016 09:43:26 GMT

That's because you are changing the schema while using the same table name. You will need to explicitly concatenate:

concatenate(Orders)

Load *

Applymap .......

from Orders ;

The problem has nothing to do with the applymap.

Re: new table when using applymap with resident

MindaugasBacius — Fri, 29 Jul 2016 09:51:58 GMT

I am not sure if you really need such a script.

Maybe that's a special needs for your Data model but I would rather make it more clean:

map_city_to_addres:
Mapping LOAD * INLINE [
    number, city
    1, city1
    2, city2
    3, city3
    4, city4
    5, city5
];

address:
LOAD *,
Applymap('map_city_to_addres',number,null()) as city 
INLINE [
number,addres
1,address1
2,address2
3,address3
4,address4
5,address5
];

Table view:

Re: new table when using applymap with resident

curiousfellow — Fri, 29 Jul 2016 10:05:45 GMT

I think I really need the script

This code script is a simplified version of the real script.

In my case I need to resuse the table to add fields to it.

Re: new table when using applymap with resident

MindaugasBacius — Fri, 29 Jul 2016 10:31:02 GMT

Try like this maybe:

address:
LOAD * INLINE [
number,addres
1,address1
2,address2
3,address3
4,address4
5,address5
];


city:
LOAD * INLINE [
    number, city
    1, city1
    2, city2
    3, city3
    4, city4
    5, city5
];


map_city_to_addres:
Mapping Load number, city resident city;

QUALIFY *;
addres:
load *,
Applymap('map_city_to_addres',number,null()) as city
resident address;
UNQUALIFY *;

drop table city;

Result:

Or instead of Qualify rename the fields.

Re: new table when using applymap with resident

rubenmarin — Fri, 29 Jul 2016 10:44:36 GMT

Hi, in case it helps: You can use several Applymap in the same LOAD sentence, and also you can use AplpyMap inseide an applymap:

LOAD *,

Applymap('Map_City', FieldID) as City,

Applymap('Map_Country', Applymap('Map_City', FieldID)) as Country,

Another technique is using precedent load, wich one load uses the data from the LOAD after it:

LOAD *, ApplyMap('Map_Country', City) as Country; // Here City field is used. (And loaded because of '*')

LOAD *, Applymap('Map_City', FieldID) as City // Here City field is generated

Re: new table when using applymap with resident

curiousfellow — Fri, 29 Jul 2016 10:55:51 GMT

When I use your suggestion , new rows are added. I want the city added to the row with the corresponding number in table address.

Re: new table when using applymap with resident

curiousfellow — Fri, 29 Jul 2016 11:00:18 GMT

Sorry for not being clear. What i want to prevent is that a new table is being created.

In the script of Henric Cronström, will there a new table Orders-1 be created ?