Convert to mapping load

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[sasiparupudi1 · Accepted solution]

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

[Anonymous]

Bingo, thank you!