Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Replacing Nulls when Joining two tables

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?

2 Replies

Re: Replacing Nulls when Joining two tables

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.

Not applicable

Re: Replacing Nulls when Joining two tables

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. 

Community Browser