topic Re: Convert to mapping load in QlikView

Convert to mapping load

Anonymous — Tue, 01 Sep 2015 20:29:48 GMT

I would like to get rid of the table "Deinstalled_IP" and map this value directly into the Locations table. SVMXC_IP_Site_Id_Key is the key field to SVMXC_Site_Id_Key. I know this is simple but I seem to be missing something.

Deinstalled_IP:

LOAD

SVMXC_IP_Site_Id_Key,

Count(DISTINCT SVMXC_IP_Id_Key) as Deinstalled_IP

FROM

[... REP_SVMXC_Installed_Product.qvd](qvd)

Where (SVMXC_IP_Status = 'Deinstalled')

Group By SVMXC_IP_Site_Id_Key;

Locations:

LOAD

SVMXC_Site_Id_Key,

xxx

Re: Convert to mapping load

Anonymous — Tue, 01 Sep 2015 20:33:12 GMT

use this:

Deinstalled_IP:

mapping LOAD

SVMXC_IP_Site_Id_Key,

Count(DISTINCT SVMXC_IP_Id_Key) as Deinstalled_IP

FROM

[... REP_SVMXC_Installed_Product.qvd](qvd)

Where (SVMXC_IP_Status = 'Deinstalled')

Group By SVMXC_IP_Site_Id_Key;

Locations:

LOAD

applymap('Deinstalled_IP',SVMXC_Site_Id_Key,) as Deinstalled_IP

xxx

Re: Convert to mapping load

Anonymous — Tue, 01 Sep 2015 20:54:10 GMT

Hmmm. This appends the Site_Id_Key from the Deinstalled_IP table into the Locations table, rather than the count of IP at Locations with Status Deinstalled.

Re: Convert to mapping load

Anonymous — Tue, 01 Sep 2015 20:59:27 GMT

do you know about applymap?

you give Name of table and field for Input to mapping table. you get the second field of your mapping table as Output

refer to QlikView help:

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

ApplyMap ('map1', 5, 'xxx') returns 'xxx'

ApplyMap ('map1', 1, 'xxx') returns 'one'

ApplyMap ('map1', 5, null( ) ) returns NULL

Re: Convert to mapping load

sasiparupudi1 — Thu, 03 Sep 2015 19:31:35 GMT

Try like this

Deinstalled_IP:

mapping LOAD

SVMXC_IP_Site_Id_Key,

Count(DISTINCT SVMXC_IP_Id_Key) as Deinstalled_IP

FROM

[... REP_SVMXC_Installed_Product.qvd](qvd)

Where (SVMXC_IP_Status = 'Deinstalled')

Group By SVMXC_IP_Site_Id_Key;

Locations:

LOAD

SVMXC_Site_Id_Key,

ApplyMap('Deinstalled_IP',SVMXC_Site_Id_Key,0) as Deinstalled_IP

resident xxx;

drop Table Deinstalled_IP;

hth

Sasi

Re: Convert to mapping load

Anonymous — Thu, 03 Sep 2015 20:11:55 GMT

Bingo, thank you!