topic Re: Script Doubt in QlikView

Script Doubt

nikhilgarg — Thu, 02 Apr 2015 08:34:15 GMT

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

Re: Script Doubt

MK_QSL — Thu, 02 Apr 2015 08:44:02 GMT

Table1:

Mapping Load SalesPersonId, SalesPersonName From TableName;

Table2:

Mapping Load SalesPersonId, SalesPersonName From TableName;

Final:

Load

SalesPersonId,

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

FromTableName2:

Concatenate

Load

SalespersonId,

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

From TableName3;

Re: Script Doubt

anbu1984 — Thu, 02 Apr 2015 08:47:48 GMT

Load

SalesPersonId,

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

desk ;

Re: Script Doubt

nikhilgarg — Thu, 02 Apr 2015 08:49:30 GMT

i dont think this will work becoz in this case 2 cases can be:

Unkknown-SalesPersonNAme and SalesPErsonName-Unknown

Re: Script Doubt

Peter_Cammaert — Thu, 02 Apr 2015 08:50:24 GMT

Yes

Load SalesPersonId,

Applymap('Table1' , SalesPersonId , 'Unknown') & ',' &

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

desk ;

Re: Script Doubt

anbu1984 — Thu, 02 Apr 2015 09:01:29 GMT

So you will get SalespersonName from only one of tables?

If(Applymap('Table1' , SalesPersonId , 'Unknown') = 'Unknown',

Applymap('Table2' , SalesPersonId , 'Unknown') & '-Unknown',

If(Applymap('Table2' , SalesPersonId , 'Unknown') = 'Unknown',

Applymap('Table1' , SalesPersonId , 'Unknown') & '-Unknown',

Applymap('Table1' , SalesPersonId ) & '-' & Applymap('Table2' , SalesPersonId)) as SalesPersonName

Re: Script Doubt

nikhilgarg — Thu, 02 Apr 2015 09:08:31 GMT

Hey,

I want that is salespersonid is present in Table1 then it should place SalesPErsonName corresponding to it else if not then ,

if it is present in table2 then it should place SalesPErsonName corresponding to it else if not then , Unknow.

Can you pls tell the whole script.

Thanks

Re: Script Doubt

anbu1984 — Thu, 02 Apr 2015 09:15:13 GMT

If(Applymap('Table1' , SalesPersonId , 'Unknown') <> 'Unknown',

Applymap('Table1' , SalesPersonId ),

If(Applymap('Table2' , SalesPersonId , 'Unknown') <> 'Unknown',

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

Re: Script Doubt

Peter_Cammaert — Thu, 02 Apr 2015 09:41:59 GMT

Or even simpler/more compact:

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

;

Re: Script Doubt

nikhilgarg — Thu, 02 Apr 2015 09:45:37 GMT

Thanks peter. It should work

Re: Script Doubt

Thu, 02 Apr 2015 10:46:18 GMT

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;

Re: Script Doubt

Colin-Albert — Thu, 02 Apr 2015 11:08:09 GMT

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 .......

Re: Script Doubt

Colin-Albert — Thu, 02 Apr 2015 11:17:03 GMT

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.

Re: Script Doubt

nikhilgarg — Thu, 02 Apr 2015 11:18:48 GMT

Thanks Colin. But i hope this also works:

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

Re: Script Doubt

nikhilgarg — Thu, 02 Apr 2015 11:22:02 GMT

If i write in script:

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

Re: Script Doubt

Colin-Albert — Thu, 02 Apr 2015 11:24:43 GMT

'null' is comparing a text string containing letters null

to compare a null value use

If(salesregion = '' , 'Unknown')

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

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