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?
