Replacing Nulls when Joining two tables

Mon, 11 Aug 2014 15:03:56 GMT

I am wondering if there is an easier/better way to accomplish replacing null values when joining two tables. Here is the situation:

I have a dimension table in the database and I want to apply default values for any null value when applying a mapping table to the dimension table. I need to map several columns so I am not using ApplyMap (although I suppose I could create 5 mapping tables then use them to default the values for each column).

Using a SQL Query I would simple join the two tables and use the isnull function:

Select

BOB_ID

,BOB_DESC

,isnull([Client Name Abr],'New Client') as [Client Name Abr]

,ISNULL([Client Name]), 'New Client') as [Client Name]

FROM "DM_LEDGER"."BLOCK_OF_BUSINESS" d

inner join Structure s on s.BOB_ID = d.BOB_ID

Where d.LDR_ENTITY_ID = '08304';

I am having a harder time finding a simple way to pull this off with Qlikview. For now, I am loading the dimension table, then left joining th e structure table, and finally loading into the final table using an IF statement to replace the nulls:

Clienttemp:

LOAD "BOB_ID",

"BOB_DESC";

SQL SELECT "BOB_ID",

"BOB_DESC"

FROM "DM_LEDGER"."BLOCK_OF_BUSINESS"

Where LDR_ENTITY_ID = '08304';

ClientStructure:

LOAD * INLINE [

BOB_ID, Client Name Abr, Client Name, PC Owner Abr, PC Owner, ClientSort

AFAS000004, BBT, BBandT Loan Services, LA, Loe Arr, 1

AFAS000037, BSI, BSI, LA, Loe Arr, 1

AFAS000012, 5th 3rd, Fifth Third, LA, Loe Arr, 1

AFAS000015, HomeS, "Home Servicing, LLC", LA, Loe Arr, 1

AFAS000017, Llive, Lender Live, LA, Loe Arr, 1

AFAS000021, Ocwen, Ocwen, LA, Loe Arr, 1

AFAS000022, OWB, One West Bank, LA, Loe Arr, 1

AFAS000025, PMH, PMH Financial, LA, Loe Arr, 1

];

Left Join (Clienttemp) Load

BOB_ID,

[Client Name Abr],

[Client Name],

[PC Owner Abr],

[PC Owner],

ClientSort

Resident ClientStructure;

drop table ClientStructure;

Client:

NoConcatenate

Load

BOB_ID, BOB_DESC

,IF(ISNULL([Client Name Abr]),'New Client',[Client Name Abr]) as [Client Name Abr]

,IF(ISNULL([Client Name]), 'New Client', [Client Name]) as [Client Name]

,IF(ISNULL([PC Owner Abr]), 'New', [PC Owner Abr]) as [PC Owner Abr]

,IF(ISNULL([PC Owner]), 'New Client', [PC Owner]) as [PC Owner]

,IF(ISNULL([ClientSort]), 99, [ClientSort]) as [ClientSort]

Resident Clienttemp;

DROP Table Clienttemp;

Is there an easier/better way to do this?

Re: Replacing Nulls when Joining two tables

Colin-Albert — Mon, 11 Aug 2014 15:18:49 GMT

It can be easier to use multiple ApplyMap statements to resolve default values rather than using joins.

You can always keep the existing solution on the SQL side of the query.

Re: Replacing Nulls when Joining two tables

Mon, 11 Aug 2014 15:25:24 GMT

Thanks Colin.

I would keep in the SQL side, except the structure table does not exist in the database. I will try using several mapping tables.

topic Re: Replacing Nulls when Joining two tables in QlikView

Replacing Nulls when Joining two tables

Re: Replacing Nulls when Joining two tables

Re: Replacing Nulls when Joining two tables