topic ApplyMap or Join Only If Null in QlikView

ApplyMap or Join Only If Null

sspringer — Tue, 15 May 2012 21:54:38 GMT

How do I conditionally apply a map or join?

I have 2 tables, 1 is my master data which has 2 columns - 1 with an OfficeID and 1 with OfficeName. Sometimes OfficeID is populated and other times OfficeName. I have a second table (link table) that has the list of OfficeID's and corresponding names. I want to apply a map or populate all of the NULL OfficeName records with the information from the second table. What is the best way to accomplish this?

Thank you!

Re: ApplyMap or Join Only If Null

Wed, 16 May 2012 10:56:20 GMT

Hi,

can office names be the same for different ids?

don't know is this the best way, but you can solve it with two mapping tables:

OFFICE:

LOAD * INLINE [

ID, NAME

1, office_1

2, office_2

3, office_3

4, office_4

5, office_5

6, office_6

];

Office_Map_by_id:

Mapping LOAD

ID, NAME Resident OFFICE;

Office_Map_by_name:

Mapping LOAD

NAME, ID Resident OFFICE;

TEST:

LOAD * INLINE [

F1, F2

, office_1

, office_4

, office_5

];

TEST_RESULT:

LOAD if (IsNull(F1) or trim(F1)='', ApplyMap('Office_Map_by_name', F2, null()), F1) as result_id,

if(IsNull(F2) or trim(F2)='', ApplyMap('Office_Map_by_id', F1, null()), F2) as result_name

Resident TEST;

regards

ApplyMap or Join Only If Null

jagannalla — Wed, 16 May 2012 11:18:43 GMT

If i understood your concept, you can achive this using mapping concept.

For example:

Office:

Load OfficeID, OfficeName from Office:

Map1:

Mapping OfficeID,OfficeName Resident Office;

Office2:

Load *, ApplyMap('Map1',OfficeID,Null()) as OfficeName

From LinkTable;

ApplyMap or Join Only If Null

Wed, 16 May 2012 12:25:28 GMT

Hi sspringer,

Use isnull() clause to check where the office name is null and then use applymap to map it with office names as shown below in the load statement:

Load

if(isnull(office name)=-1, applymap())

Hope this help,

Anosh

ApplyMap or Join Only If Null

sspringer — Wed, 16 May 2012 13:14:14 GMT

I'm still not sure this solution works. I tried this if(isnull(office name)=-1, applymap()) , however, if I use this and alias it as OfficeName, then I cannot also load OfficeName in the script above for rows that already have an OfficeName. The problem is this: I have a table with 20 rows, 10 rows already have OfficeName, but no OfficeID (those are fine). 10 rows have OfficeID but no OfficeName - I need to fill in those OfficeName's using a different table.

Any more ideas? Thank you so much for responding!

ApplyMap or Join Only If Null

Thu, 17 May 2012 15:11:55 GMT

Use this:

if (Trim(OfficeName)='', ApplyMap('Office_Map_by_id', Id),OfficeName) as OfficeName

It will work.

Regards,

Anosh