Script Doubt

[nikhilgarg]

Hey,

I have a script:

Load

SalesPersonId,

Applymap('Table1' , SalesPersonId , 'Unknown') as SalesPersonName,

concat

Applymap('Table2' , SalesPersonId , 'Unknown') as SalesPersonName,

desk ;

I ahve Salespersonname coming form table 1 and table2 so can i concat like above script to get a single field SalesPerson with values from both table1 and table 2??

Thanks

[#2]

Hi,

You can try below script.

Table1:

Mapping Load

         SalesPersonId,

         SalesPersonName

From TableName;

Table2:

Mapping Load 

         SalesPersonId,

         SalesPersonName

From TableName;

COMMENT TABLES USING Table1;

COMMENT TABLES USING Table2;

New_Table:

Load

     SalesPersonId,

     Applymap('Table1' , SalesPersonId , 'Unknown') as SalesPersonName

From Sourcepath;

Concatenate(New_Table)

Load

     SalespersonId,

     Applymap('Table2' , SalesPersonId , 'Unknown') as SalesPersonName

From Sourcepath;

[Colin-Albert]

You can use QlikView concatenation to merge to two sets of mapping data into a single mapping table which simplifies the script.

// This will concatenate the data from Table1 & Table2 to a single mapping table Table_Map

// as the field names are identical in both tables

Table_Map:

Mapping Load

     SalesPersonId, SalesPersonName

From Table1;

Table_Map:             

Mapping Load

     SalesPersonId, SalesPersonName

From Table2;

Result:

Load

SalesPersonId,

Applymap('Table_Map', SalesPersonId , 'Unknown') as SalesPersonName,

desk ;

from .......

[Colin-Albert]

I have just done some more tests on this, and unlike normal tables which will auto concatenate when the field names match, to get the mapping table to concatenate, the table name  XXX: must match.

The field names in the mapping tables are ignored they are simply loaded as Column1 & Column2 !

Table_Map:

Mapping Load

     SalesPersonId, SalesPersonName

From Table1;

Table_Map:            

Mapping Load

     SalesPersonId as Apples , SalesPersonName as Pears

From Table2;

This script will still create a single mapping table named 'Table_Map', combining the data from Table1 and Table2.

[nikhilgarg]

Thanks Colin. But i hope this also works:

Applymap('Table1' , SalesPersonId , Applymap('Table2' , SalesPersonId,'Unknown')) as SalesPersonName

[nikhilgarg]

If i write in script:

If(salesregion = 'null' , 'Unknown') is it ok in case if i have no values in salesregion field ??

[Colin-Albert]

'null' is comparing a text string containing letters null

to compare a null value use

     If(salesregion = '' , 'Unknown')

or

     If(salesregion = null() , 'Unknown')

or

     If(len(trim(salesregion)) = 0, 'Unknown')